By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Dynamic SQL

P: n/a
I'm writing a stored procedure that uses DynamicSQL using the
following code:

SET @Get_Role_Number = 'Select @Role_Number = ' + @DatabaseName +
'.dbo.sysusers.uid FROM ' + @DatabaseName + '.dbo.sysusers WHERE ' +
@DatabaseName + '.dbo.sysusers.[name] = ' + @ApplicationRole

Execute sp_executeSQL @Get_Role_Number, N'@Role_Number int output,
@DatabaseName varchar(50), @ApplicationRole varchar(50)', @Role_Number
OUTPUT, @DatabaseName, @ApplicationRole

The column name in the table is "name" and I get a message that there
is no such columnname and it gives the value of the parameter passed
as the column name. Is there any way around this?
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Susan (fe*******@subway.com) writes:
I'm writing a stored procedure that uses DynamicSQL using the
following code:

SET @Get_Role_Number = 'Select @Role_Number = ' + @DatabaseName +
'.dbo.sysusers.uid FROM ' + @DatabaseName + '.dbo.sysusers WHERE ' +
@DatabaseName + '.dbo.sysusers.[name] = ' + @ApplicationRole

Execute sp_executeSQL @Get_Role_Number, N'@Role_Number int output,
@DatabaseName varchar(50), @ApplicationRole varchar(50)', @Role_Number
OUTPUT, @DatabaseName, @ApplicationRole

The column name in the table is "name" and I get a message that there
is no such columnname and it gives the value of the parameter passed
as the column name. Is there any way around this?


Yes. Did you ever look at the SQL code you generated? When working with
dynamic SQL, it's usually a big time-saver to throw in a @debug parameter
and then

IF @debug = 1
PRINT @Get_Role_Number

You should not include the value of @ApplicationRole in the SQL string,
if you want to pass it as a parameter to the dynamic SQL. You should
include the name of the parameter to the dynamic SQL string.

On the other hand, you cannot pass @DatabaseName as parameter to the
dynamic SQL, but must include it the string, as SQL Server does not
permit variables in these positions.

Also look at http://www.algonet.se/~sommar/dynami...#sp_executesql.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

P: n/a
fe*******@subway.com (Susan) wrote in message news:<77**************************@posting.google. com>...
I'm writing a stored procedure that uses DynamicSQL using the
following code:

SET @Get_Role_Number = 'Select @Role_Number = ' + @DatabaseName +
'.dbo.sysusers.uid FROM ' + @DatabaseName + '.dbo.sysusers WHERE ' +
@DatabaseName + '.dbo.sysusers.[name] = ' + @ApplicationRole

Execute sp_executeSQL @Get_Role_Number, N'@Role_Number int output,
@DatabaseName varchar(50), @ApplicationRole varchar(50)', @Role_Number
OUTPUT, @DatabaseName, @ApplicationRole

The column name in the table is "name" and I get a message that there
is no such columnname and it gives the value of the parameter passed
as the column name. Is there any way around this?


This should work:

declare @Get_Role_Number nvarchar(1000),
@DatabaseName sysname,
@ApplicationRole sysname,
@Role_Number int

set @DatabaseName = 'MyDB'
set @ApplicationRole = 'myRole'

SET @Get_Role_Number = 'Select @Role_Number = uid FROM ' +
@DatabaseName + '.dbo.sysusers WHERE [name] = @ApplicationRole'

Execute sp_executeSQL
@stmt = @Get_Role_Number,
@params = N'@Role_Number int output, @ApplicationRole varchar(50)',
@Role_Number = @Role_Number OUTPUT,
@ApplicationRole = @ApplicationRole

select @Role_Number
Simon
Jul 20 '05 #3

P: n/a
Thanks so much. It worked.

Susan

sq*@hayes.ch (Simon Hayes) wrote in message news:<60**************************@posting.google. com>...
fe*******@subway.com (Susan) wrote in message news:<77**************************@posting.google. com>...
I'm writing a stored procedure that uses DynamicSQL using the
following code:

SET @Get_Role_Number = 'Select @Role_Number = ' + @DatabaseName +
'.dbo.sysusers.uid FROM ' + @DatabaseName + '.dbo.sysusers WHERE ' +
@DatabaseName + '.dbo.sysusers.[name] = ' + @ApplicationRole

Execute sp_executeSQL @Get_Role_Number, N'@Role_Number int output,
@DatabaseName varchar(50), @ApplicationRole varchar(50)', @Role_Number
OUTPUT, @DatabaseName, @ApplicationRole

The column name in the table is "name" and I get a message that there
is no such columnname and it gives the value of the parameter passed
as the column name. Is there any way around this?


This should work:

declare @Get_Role_Number nvarchar(1000),
@DatabaseName sysname,
@ApplicationRole sysname,
@Role_Number int

set @DatabaseName = 'MyDB'
set @ApplicationRole = 'myRole'

SET @Get_Role_Number = 'Select @Role_Number = uid FROM ' +
@DatabaseName + '.dbo.sysusers WHERE [name] = @ApplicationRole'

Execute sp_executeSQL
@stmt = @Get_Role_Number,
@params = N'@Role_Number int output, @ApplicationRole varchar(50)',
@Role_Number = @Role_Number OUTPUT,
@ApplicationRole = @ApplicationRole

select @Role_Number
Simon

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.