Please Check my store Procedure !

I have a store Procedure modify structTable but check syntax is error !

Please help me ?
--
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME ='MODISTRCTTABLE ' AND TYPE='P')
BEGIN
DROP PROCEDURE MODISTRUCT_TABLE
END
GO

CREATE PROCEDURE MODISTRUCT_TABLE
@.TABLE_NAME VARCHAR(60),
@.COLUMN_NAME VARCHAR(60),
@.COLUMN_TYPE VARCHAR(60),
@.COLUMN_SIZE INT(10)
AS
BEGIN
IF EXISTS (SELECT @.COLUMN_NAME FROM syscolumns)
BEGIN
ALTER TABLE @.TABLE_NAME ALTER @.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')'
END
ELSE
BEGIN
ALTER TABLE @.TABLE_NAME ADD @.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')'
END
END

-

CREATE PROCEDURE ENCRYPTION getString()

AS
SELECT * FORM CUSTOMER

-

Funtion getString() return a string is " With Encryption"?

Do I call function getString() in here ?

You need to use dynamic sql for this.

Code Snippet

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME ='MODISTRCTTABLE ' AND TYPE='P')
BEGIN
DROP PROCEDURE MODISTRUCT_TABLE
END
GO

CREATE PROCEDURE MODISTRUCT_TABLE
@.TABLE_NAME VARCHAR(60),
@.COLUMN_NAME VARCHAR(60),
@.COLUMN_TYPE VARCHAR(60),
@.COLUMN_SIZE INT(10)
AS
BEGIN
IF EXISTS (SELECT @.COLUMN_NAME FROM syscolumns)
BEGIN
exec ('ALTER TABLE '+@.TABLE_NAME+' ALTER '+@.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')' )
END
ELSE
BEGIN
exec('ALTER TABLE '+@.TABLE_NAME+' ADD '+@.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')' )
END
END

The other proc is also incorrect. It should be like so.

Code Snippet

CREATE PROCEDURE getString()
with ENCRYPTION
AS
SELECT * FROM CUSTOMER

|||Thanks Oj my idea is not clearly ! I have a Store procedure :
--
Create procedure MyEnCryption With Encryption
AS
Begin
Select * from Customer
End

I want replace string " With Encryption" with calling Function GetString(); Function GetString() return a string is "With Encryption"

this store rewrite :
-
Create procedure MyEnCryption GetString()
AS
Begin
Select * from Customer
End

--

Is this store run ?|||

What you're trying to do is not possible. The syntax to create a procedure is like so:

CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

|||

While creating the objects you have to use the proper syntax what is already defined..

You can achive your desired output if you create your object using dynamic SQL...

But it is not recommanded to do this.. & I didn’t understand why you need this logic..

Note:

When you encrypt the procedure/function/view/trigger you have to store your soruce code on filesystem or source safe for future reference, you never get back the soruce text from the SQL Server. So, it is really bad idea to have dynamic sql to create encrypted objects.

Code Snippet

Declare @.sql as varchar(8000)

Set @.sql = 'Create procedure MyEnCryption ' +GetString() + '

AS

Begin

Select * from Customer

End'

exec (@.sql)

|||

Thanks Admin !

Now I'am understading !

PK columns dont show up in INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Having a database user 'myuser' beeing a member of the roles 'public'
and 'db_owner' I created the test table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[myuser].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [myuser].[TEST]
GO
CREATE TABLE [myuser].[TEST] (
[TEST_ID] [varchar] (2) NOT NULL ,
[DESCRIPTION] [varchar] (60) NOT NULL ,
CONSTRAINT [TEST_PK] PRIMARY KEY CLUSTERED
(
[TEST_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
However, the primary key constraint 'TEST_PK' does not show up in the
view
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
only foreign keys (from other tables) show up.
Is this a security issue?
Using SQL Server 2000 Dev Edition SP3a on Win XP Prof.
Thank you in advance for your assistance,
SRSoenke,
This happens when a user tries to get schema information from tables
that they don't own. If you login as myuser it works fine. Can you
create the table as dbo.[TEST]? If you do this then it should work
without issue.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Soenke Richardsen wrote:
> Having a database user 'myuser' beeing a member of the roles 'public'
> and 'db_owner' I created the test table:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[myuser].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable
') =
> 1)
> drop table [myuser].[TEST]
> GO
> CREATE TABLE [myuser].[TEST] (
> [TEST_ID] [varchar] (2) NOT NULL ,
> [DESCRIPTION] [varchar] (60) NOT NULL ,
> CONSTRAINT [TEST_PK] PRIMARY KEY CLUSTERED
> (
> [TEST_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> However, the primary key constraint 'TEST_PK' does not show up in the
> view
> select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> only foreign keys (from other tables) show up.
> Is this a security issue?
> Using SQL Server 2000 Dev Edition SP3a on Win XP Prof.
> Thank you in advance for your assistance,
> SR|||Hi Mark,
during the last week I tried several times to reply to your message
using google groups, but always got a message like:
"Unable to retrieve message OQ0Dm2T$EHA.3180@.TK2MSFTNGP10.phx.gbl"
Now I found the new beta groups, and they seem to work better...
Anyway, your posting helped me, thanks!
Soenke
Mark Allison wrote:[vbcol=seagreen]
> Soenke,
> This happens when a user tries to get schema information from tables
> that they don't own. If you login as myuser it works fine. Can you
> create the table as dbo.[TEST]? If you do this then it should work
> without issue.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Soenke Richardsen wrote:
'public'[vbcol=seagreen]
N'IsUserTable') =[vbcol=seagreen]
the[vbcol=seagreen]

Pivot or Transpose a row

I would like to transpose/pivot a table that exists:

2 1 2 2 5
3 3 3 1 5
2 2 2 1 4
4 4 4 5 1
2 2 2 1 4
2 2 1 5 5

to:

1 1 2

1 2 3

1 3 2

1 4 4

1 5 2

1 6 2

2 1 1

2 2 3

2 3 2

2 4 4

2 5 2

2 6 2

Just looking at the first 6 rows of the new table:

Basically every column is copied to the third column of the new table.( 1,1) becomes (1,3)....(2,1) becomes (2,3)

There are 6 rows in the original table so the second column in the new table is a reoccuring count from 1 to 6. There is a value of 1 in the first column of the new table(first 6 rows).

The logic in the old table is: every column contains 6 answers from a particular user.

In the new table (1,1) represents the user, (1,2) represents the question, (1,3) represents the users answer for the question.

Is there a quick way to do this in Transact sql using a cursor and the and pivot function?

Any help would be appreciated!

There is not such concept as row number in SQL Server, so we will need a criteria to sort the table. Let us suppose that the table has a column with identity property, then we can use:

Code Snippet

create table dbo.t1 (

row_id int not null identity primary key,

c1 int,

c2 int,

c3 int,

c4 int,

c5 int

)

go

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 1, 2, 2, 5)

insert into dbo.t1(c1, c2, c3, c4, c5) values(3, 3, 3, 1, 5)

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 2, 2, 1, 4)

insert into dbo.t1(c1, c2, c3, c4, c5) values(4, 4, 4, 5, 1)

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 2, 2, 1, 4)

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 2, 1, 5, 5)

go

select

b.c1 as c_id,

a.row_id,

case b.c1

when 1 then a.c1

when 2 then a.c2

when 3 then a.c3

when 4 then a.c4

when 5 then a.c5

end as c3

from

dbo.t1 as a

cross join

(

select 1 as c1

union all

select 2 as c1

union all

select 3 as c1

union all

select 4 as c1

union all

select 5 as c1

) as b

order by

c_id,

a.row_id

go

-- SS 2005

select

c_id,

row_id,

val

from

(

select

row_id,

c1 as [1],

c2 as [2],

c3 as [3],

c4 as [4],

c5 as [5]

from

dbo.t1

) as pvt

unpivot

(val for c_id in ([1], [2], [3], [4], [5])) as unpvt

order by

c_id,

row_id

go

drop table dbo.t1

go

AMB

|||

Here is one way:

select n,
row_number() over ( partition by n order by current_timestamp ),
case n when 1 then c1
when 2 then c2
when 3 then c3
when 4 then c4
when 5 then c5
end
from tbl cross join
( select 1 union
select 2 union
select 3 union
select 4 union
select 5 ) D ( n )

Here tbl is your table and c1, c2, ... denotes the columns. The row_number() function is used to number the tables and the order by current_timestamp is simply a shortcut to generate the sequence without any reliance on existing columns.

--

Anith

|||

Hello

This works too:

SELECT IDENTITY(int, 1,1) AS ID_Num, t.*
INTO #TestResults
FROM TestResults t

CREATE TABLE dbo.NewResults (iUser INT, iQuestion INT, iAnswer INT)

DECLARE @.iColumns INT, @.lcSQL VARCHAR(200)
SET @.iColumns = 1

WHILE @.iColumns <= 5 -- Number of result columns in table TestResults
BEGIN
SET @.lcSQL = 'INSERT INTO dbo.NewResults (iUser, iQuestion, iAnswer) SELECT 1, ID_Num, c' + CAST(@.iColumns AS VARCHAR(2)) + ' FROM #TestResults'
EXEC(@.lcSQL)
SET @.iColumns = @.iColumns + 1
END

DROP TABLE #TestResults

SELECT * FROM dbo.NewResults

|||

Anith could you please explain the sql syntax?

over ( partition by n order by current_timestamp

and

D ( n )

thanks!

Pivot or Transpose a row

I would like to transpose/pivot a table that exists:

2 1 2 2 5
3 3 3 1 5
2 2 2 1 4
4 4 4 5 1
2 2 2 1 4
2 2 1 5 5

to:

1 1 2

1 2 3

1 3 2

1 4 4

1 5 2

1 6 2

2 1 1

2 2 3

2 3 2

2 4 4

2 5 2

2 6 2

Just looking at the first 6 rows of the new table:

Basically every column is copied to the third column of the new table.( 1,1) becomes (1,3)....(2,1) becomes (2,3)

There are 6 rows in the original table so the second column in the new table is a reoccuring count from 1 to 6. There is a value of 1 in the first column of the new table(first 6 rows).

The logic in the old table is: every column contains 6 answers from a particular user.

In the new table (1,1) represents the user, (1,2) represents the question, (1,3) represents the users answer for the question.

Is there a quick way to do this in Transact sql using a cursor and the and pivot function?

Any help would be appreciated!

There is not such concept as row number in SQL Server, so we will need a criteria to sort the table. Let us suppose that the table has a column with identity property, then we can use:

Code Snippet

create table dbo.t1 (

row_id int not null identity primary key,

c1 int,

c2 int,

c3 int,

c4 int,

c5 int

)

go

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 1, 2, 2, 5)

insert into dbo.t1(c1, c2, c3, c4, c5) values(3, 3, 3, 1, 5)

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 2, 2, 1, 4)

insert into dbo.t1(c1, c2, c3, c4, c5) values(4, 4, 4, 5, 1)

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 2, 2, 1, 4)

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 2, 1, 5, 5)

go

select

b.c1 as c_id,

a.row_id,

case b.c1

when 1 then a.c1

when 2 then a.c2

when 3 then a.c3

when 4 then a.c4

when 5 then a.c5

end as c3

from

dbo.t1 as a

cross join

(

select 1 as c1

union all

select 2 as c1

union all

select 3 as c1

union all

select 4 as c1

union all

select 5 as c1

) as b

order by

c_id,

a.row_id

go

-- SS 2005

select

c_id,

row_id,

val

from

(

select

row_id,

c1 as [1],

c2 as [2],

c3 as [3],

c4 as [4],

c5 as [5]

from

dbo.t1

) as pvt

unpivot

(val for c_id in ([1], [2], [3], [4], [5])) as unpvt

order by

c_id,

row_id

go

drop table dbo.t1

go

AMB

|||

Here is one way:

select n,
row_number() over ( partition by n order by current_timestamp ),
case n when 1 then c1
when 2 then c2
when 3 then c3
when 4 then c4
when 5 then c5
end
from tbl cross join
( select 1 union
select 2 union
select 3 union
select 4 union
select 5 ) D ( n )

Here tbl is your table and c1, c2, ... denotes the columns. The row_number() function is used to number the tables and the order by current_timestamp is simply a shortcut to generate the sequence without any reliance on existing columns.

--

Anith

|||

Hello

This works too:

SELECT IDENTITY(int, 1,1) AS ID_Num, t.*
INTO #TestResults
FROM TestResults t

CREATE TABLE dbo.NewResults (iUser INT, iQuestion INT, iAnswer INT)

DECLARE @.iColumns INT, @.lcSQL VARCHAR(200)
SET @.iColumns = 1

WHILE @.iColumns <= 5 -- Number of result columns in table TestResults
BEGIN
SET @.lcSQL = 'INSERT INTO dbo.NewResults (iUser, iQuestion, iAnswer) SELECT 1, ID_Num, c' + CAST(@.iColumns AS VARCHAR(2)) + ' FROM #TestResults'
EXEC(@.lcSQL)
SET @.iColumns = @.iColumns + 1
END

DROP TABLE #TestResults

SELECT * FROM dbo.NewResults

|||

Anith could you please explain the sql syntax?

over ( partition by n order by current_timestamp

and

D ( n )

thanks!