Dynamic SQL

[Susan]

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

SET @Get_Role_Numbe r = 'Select @Role_Number = ' + @DatabaseName +
'.dbo.sysusers. uid FROM ' + @DatabaseName + '.dbo.sysusers WHERE ' +
@DatabaseName + '.dbo.sysusers.[name] = ' + @ApplicationRol e

Execute sp_executeSQL @Get_Role_Numbe r, N'@Role_Number int output,
@DatabaseName varchar(50), @ApplicationRol e varchar(50)', @Role_Number
OUTPUT, @DatabaseName, @ApplicationRol e

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]

Re: Dynamic SQL

Susan (fennell_s@subw ay.com) writes:[color=blue]
> I'm writing a stored procedure that uses DynamicSQL using the
> following code:
>
> SET @Get_Role_Numbe r = 'Select @Role_Number = ' + @DatabaseName +
> '.dbo.sysusers. uid FROM ' + @DatabaseName + '.dbo.sysusers WHERE ' +
> @DatabaseName + '.dbo.sysusers.[name] = ' + @ApplicationRol e
>
> Execute sp_executeSQL @Get_Role_Numbe r, N'@Role_Number int output,
> @DatabaseName varchar(50), @ApplicationRol e varchar(50)', @Role_Number
> OUTPUT, @DatabaseName, @ApplicationRol e
>
> 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?[/color]

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_Numbe r

You should not include the value of @ApplicationRol e 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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Simon Hayes]

Re: Dynamic SQL

fennell_s@subwa y.com (Susan) wrote in message news:<776b37c8. 0312020613.22cf 56d0@posting.go ogle.com>...[color=blue]
> I'm writing a stored procedure that uses DynamicSQL using the
> following code:
>
> SET @Get_Role_Numbe r = 'Select @Role_Number = ' + @DatabaseName +
> '.dbo.sysusers. uid FROM ' + @DatabaseName + '.dbo.sysusers WHERE ' +
> @DatabaseName + '.dbo.sysusers.[name] = ' + @ApplicationRol e
>
> Execute sp_executeSQL @Get_Role_Numbe r, N'@Role_Number int output,
> @DatabaseName varchar(50), @ApplicationRol e varchar(50)', @Role_Number
> OUTPUT, @DatabaseName, @ApplicationRol e
>
> 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?[/color]

This should work:

declare @Get_Role_Numbe r nvarchar(1000),
@DatabaseName sysname,
@ApplicationRol e sysname,
@Role_Number int

set @DatabaseName = 'MyDB'
set @ApplicationRol e = 'myRole'

SET @Get_Role_Numbe r = 'Select @Role_Number = uid FROM ' +
@DatabaseName + '.dbo.sysusers WHERE [name] = @ApplicationRol e'

Execute sp_executeSQL
@stmt = @Get_Role_Numbe r,
@params = N'@Role_Number int output, @ApplicationRol e varchar(50)',
@Role_Number = @Role_Number OUTPUT,
@ApplicationRol e = @ApplicationRol e

select @Role_Number


Simon

[Susan]

Re: Dynamic SQL

Thanks so much. It worked.

Susan

sql@hayes.ch (Simon Hayes) wrote in message news:<60cd0137. 0312030011.501d 87fb@posting.go ogle.com>...[color=blue]
> fennell_s@subwa y.com (Susan) wrote in message news:<776b37c8. 0312020613.22cf 56d0@posting.go ogle.com>...[color=green]
> > I'm writing a stored procedure that uses DynamicSQL using the
> > following code:
> >
> > SET @Get_Role_Numbe r = 'Select @Role_Number = ' + @DatabaseName +
> > '.dbo.sysusers. uid FROM ' + @DatabaseName + '.dbo.sysusers WHERE ' +
> > @DatabaseName + '.dbo.sysusers.[name] = ' + @ApplicationRol e
> >
> > Execute sp_executeSQL @Get_Role_Numbe r, N'@Role_Number int output,
> > @DatabaseName varchar(50), @ApplicationRol e varchar(50)', @Role_Number
> > OUTPUT, @DatabaseName, @ApplicationRol e
> >
> > 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?[/color]
>
> This should work:
>
> declare @Get_Role_Numbe r nvarchar(1000),
> @DatabaseName sysname,
> @ApplicationRol e sysname,
> @Role_Number int
>
> set @DatabaseName = 'MyDB'
> set @ApplicationRol e = 'myRole'
>
> SET @Get_Role_Numbe r = 'Select @Role_Number = uid FROM ' +
> @DatabaseName + '.dbo.sysusers WHERE [name] = @ApplicationRol e'
>
> Execute sp_executeSQL
> @stmt = @Get_Role_Numbe r,
> @params = N'@Role_Number int output, @ApplicationRol e varchar(50)',
> @Role_Number = @Role_Number OUTPUT,
> @ApplicationRol e = @ApplicationRol e
>
> select @Role_Number
>
>
> Simon[/color]