declaring variables inside a dynamic SQL statement

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 2000 Forums

SQL Server Development (2000)

declaring variables inside a dynamic SQL statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-10 : 07:41:07

Pete writes "I like the power of building a dynamic SQL statement in a stored procedure. But can I declare and use variables inside my dynamic SQL? As an example, I provide the query below. This is part of a larger query. I am trying to execute multiple queries and then return the results using variables that were assigned to the results of the individual queries. I am getting syntax errors with the following. If I just select the first variable, it works. If I try to select the second variable as well, I get the error:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I appreciate any advice on this! Thanks!

Example query:

CREATE PROCEDURE SELECT_CLMP_Lamp_Info_By_deviceUID
@deviceuid int

AS

DECLARE @SQL varchar(1000)
SELECT @SQL = 'DECLARE @bulb1_type as char(4)'
SELECT @SQL = @SQL + 'DECLARE @retail1 as money'
SELECT @SQL = @SQL + "SELECT @bulb1_type = d.bulb1_type, @retail1 = inv.price_1 from device d left join clmp_inventory inv on d.bulb1_type = inv.uid where d.uid = " + convert(varchar, @deviceuid)
SELECT @SQL = @SQL + "SELECT @bulb1_type, @retail1"
EXEC(@SQL)
GO"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 07:47:58

You need to use Single quote

DECLARE @SQL varchar(1000)
SELECT @SQL = 'DECLARE @bulb1_type as char(4) '
SELECT @SQL = @SQL + 'DECLARE @retail1 as money '
SELECT @SQL = @SQL + ' SELECT @bulb1_type = d.bulb1_type, @retail1 = inv.price_1 from device d left join clmp_inventory inv on d.bulb1_type = inv.uid where d.uid = ' + convert(varchar, @deviceuid)
SELECT @SQL = @SQL + 'SELECT @bulb1_type, @retail1'
select @sql

Why do you want to do this dynamically?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources