Dynamic SQL

[Susan]

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?

[Erland Sommarskog]

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

[Simon Hayes]

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

[Susan]

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