TomDavie
asked on
Cannot insert a variable into a select string
Working with SQLServer 2000
I am trying to do a search on a database name from within a procedure using:
Fetch Next column_name1, colum_name2 into @vDataColumn, @vDataTable
begin
declare @vTemp char(15)
set @vTemp = @vDataTable
Select @vDataColumn From @vTemp
end
Fetch Next
I cannot make the string recognize the @vTemp variable or just the @vDataTable. I keep getting an error message:Error 137: must declare the variable '@vTemp'
The variables in the Fetch statement are already declared and the variable sizes are consistent
I am trying to do a search on a database name from within a procedure using:
Fetch Next column_name1, colum_name2 into @vDataColumn, @vDataTable
begin
declare @vTemp char(15)
set @vTemp = @vDataTable
Select @vDataColumn From @vTemp
end
Fetch Next
I cannot make the string recognize the @vTemp variable or just the @vDataTable. I keep getting an error message:Error 137: must declare the variable '@vTemp'
The variables in the Fetch statement are already declared and the variable sizes are consistent
yap, you have to use dynamic querys... You can't use variables in a select statement in the place of table names or column names...
You have to use the exec or the sp_executesql commands...
they are very simple to use....
EG:
declare @sql = 'select * from ' + @tablename
exec sp_executesql @sql
exec is for simple querys, and sp_executesql has more options...
good luck,
xenon
You have to use the exec or the sp_executesql commands...
they are very simple to use....
EG:
declare @sql = 'select * from ' + @tablename
exec sp_executesql @sql
exec is for simple querys, and sp_executesql has more options...
good luck,
xenon
BTW, the @vTemp variable is not useful. You are setting it to equal an existing variable @vDataTable. You might as well use @vDataTable.
exec('Select '+ @vDataColumn +' From '+ @vDataTable)
Another thing is that for extensibility, you should use the datatype sysname or its equivalent nvarchar(128) for a table name rather than char(15). Although if you look at a system stored proc such as sp_columns, it uses nvarchar(384) which seems a bit strange.
It seems a bit strange to have a select statement within the loop of a cursor. What are you trying to achieve? There might be a better way (cursors are quite inefficient).
exec('Select '+ @vDataColumn +' From '+ @vDataTable)
Another thing is that for extensibility, you should use the datatype sysname or its equivalent nvarchar(128) for a table name rather than char(15). Although if you look at a system stored proc such as sp_columns, it uses nvarchar(384) which seems a bit strange.
It seems a bit strange to have a select statement within the loop of a cursor. What are you trying to achieve? There might be a better way (cursors are quite inefficient).
ASKER
I have tried the first option which has been successful in returning the rows that I require.
However, I now need to be able to return the data into variables rather than just output them
I have the folowing:
Fetch Next column_name1, colum_name2 into @vDataColumn, @vDataTable
begin
exec('Select '+ @vDataColumn +' From '+ @vDataTable)
end
Fetch Next
I am creating XML tags and need to output the data in a certain way.
I have tried several things without success.
However, I now need to be able to return the data into variables rather than just output them
I have the folowing:
Fetch Next column_name1, colum_name2 into @vDataColumn, @vDataTable
begin
exec('Select '+ @vDataColumn +' From '+ @vDataTable)
end
Fetch Next
I am creating XML tags and need to output the data in a certain way.
I have tried several things without success.
If you want XML, why not use SQL Server to provide it?
exec('Select '+ @vDataColumn +' From '+ @vDataTable + ' for xml auto')
exec('Select '+ @vDataColumn +' From '+ @vDataTable + ' for xml auto')
TomDavie:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think tomazsr
answered first to the question posted... You russellshome just answered to the 2nd question posted...
So , in my opinion not russellshome was the one who would've earned the points...but it looks like this was not the decision of the poster...
regards,
xenon
answered first to the question posted... You russellshome just answered to the 2nd question posted...
So , in my opinion not russellshome was the one who would've earned the points...but it looks like this was not the decision of the poster...
regards,
xenon
Try like this:
Fetch Next column_name1, colum_name2 into @vDataColumn, @vDataTable
begin
declare @vTemp char(15)
set @vTemp = @vDataTable
exec('Select '+ @vDataColumn +' From '+ @vTemp)
end
Fetch Next
Tomaz