Leo Mwangi


I want to return the results of a select statement and tie the results to a variable, is it possible

DECLARE @variable datatype

SELECT @variable = 'SELECT * FROM TABLE'

SELECT @variable

If I execute this statements i would get the "select * from table" since thats what my variable is set to but I want to return the results of the select statement as the value of the variable.

Any advice

Thanks




Re: Using variables

Louis Davidson


Nope, this is not the way SQL Server works. First question I have is who are you trying to return the result to If it is another procedure, then you will need to use a temp table (or local table variable, depending on what you are trying to do) to pass around results. (In 2008 you can pass a local table variable around as a parameter, but not in 2005).

Can you be more specific as to what you are trying to achieve







Re: Using variables

Madhu K Nair

You might wanted to try with Table Function. And also here, you must declare the variable as Table Type.

Just run this script and see the result then you can carry on from there.

Declare @V Table (Col1 int, Col2 Varchar(10))

Insert into @V Select 1,'AAA'

Insert into @V Select 1,'bbb'

Select *From @v

Madhu





Re: Using variables

Tarek Ghazali

Hi Leo,

I have checked the code above, i think you are looking to build your dynamic sql at run time,

you have to use the following code:

DECLARE @variable datatype

Set @variable = 'SELECT * FROM TABLE'

exec(@variable)

SQL Server EXECUTE command (EXEC or EXEC() function) can be used to execute a stored procedure, etc.

Note: UDF can not run dynamic SQL which are dynamically build in UDF. Temporary Tables can not be used in UDF as well. so you cannot use EXEC command in UDF. Stored Procedure are more flexibility then User Defined Functions(UDF).

Regards,

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com





Re: Using variables

Leo Mwangi

I am trying to replace the @query on xp_sendmail which is not available on xp_smtp_sendmail since i have query results that need to be part of my message body.

Terek your response is what I was looking for but I am not sure if I can now pass the results of the exec(@variable) to another varible so that it can be contained in the message varible.





Re: Using variables

Louis Davidson

Ah, well there is two different answers to this question. You cannot easily do what you want to do. For a single row, you can do this:

declare @objectId int,@name nvarchar(128)

exec sp_executeSQL

N'select @objectId = max(object_id), @name = max(name) from sys.objects',

N'@objectId int output, @name nvarchar(128) output', @objectId=@objectId output, @name = @name output

select @objectId, @name

If this is a multi-row query, then no, you cannot to it into a scalar variable. You can insert it into a variable table, but that is no help really:

declare @variableTable table (name nvarchar(128))

declare @query nvarchar(max)

set @query = 'select name from sys.objects'

insert into @variableTable

exec (@query)

select * from @variableTable

What we have done in this case was to use xp_cmdshell to execute the query using sqlcmd/isql/osql at the command line. Spool that into a file (probably with a file name that is (or contains) a guid, just to make file name generation is easy. Then attach that file to the email. That is not terribly hard, and will give you the desired reults.