Problem to pass declared variable to SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem to pass declared variable to SP

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2008-01-11 : 14:33:46

Problem to pass declared variable to stored procedure as input parameter

hi
i want declare a variable and pass it to sp_addlogin like this :

declare @x nvarchar(100)
set @x = 'login2'
--select @x
use master
go
exec sp_addlogin @x,'123'

but the following error has shown me :
Must declare the scalar variable "@x".

where does my problem and how to solve it ?

and also
i want to pass a declared variable to this statement :

grant control on database::myDB to @x

but how to do ?
thanks
thanks

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-01-11 : 14:36:38

GO Ends a block and therefore the declaration of @x try this instead.

use master
go

declare @x nvarchar(100)
set @x = 'logloglog'
exec sp_addlogin @x,'kjyg'

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

hdv212
Posting Yak Master

140 Posts

Posted - 2008-01-11 : 15:22:21

thanks jhocutt
my first problem was solved,
but how to pass it to this statement :

grant control on database::myDB to @x

full code is :

use master
go
declare @x nvarchar(100)
declare @p nvarchar(100)
set @x = 'login1'
set @p = '123'
exec sp_addlogin @x,'123'
use myDB
exec sp_adduser @x
grant control on database::myDB to @x // error in this line

thanks

hdv212
Posting Yak Master

140 Posts

Posted - 2008-01-11 : 18:19:09

can anyBody help me ?
thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-12 : 03:26:06

Try

EXEC('grant control on database::myDB to '+@x)

Madhivanan

Failing to plan is Planning to fail

hdv212
Posting Yak Master

140 Posts

Posted - 2008-01-12 : 04:20:54