• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

How can I fill a variable with the results of a scalar query in the same T-SQL script?

I just want to fill a variable declared in my T-SQL script with the results of scalar query that happens inside the same script.

Is this possible with dynamic sql?
0
David L. Hansen
Asked:
David L. Hansen
  • 5
  • 4
  • 2
  • +2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Yup, here is an example
declare @sql varchar(1000)
SELECT @sql = COALESCE(@sql+',' ,'') +NAME
FROM master..sysdatabases
print @sql

one more thing, if one of the columns is an integer , make sure that you cast it to varchar like

declare @sql varchar(1000)
SELECT @sql = COALESCE(@sql+',' ,'') +cast(intColumn as varchar )
FROM table
print @sql
0
 
ruffoneCommented:
Is this what your are asking?
declare @personId int
SELECT @personId = personId FROM People WHERE personId =784
 
0
 
Daniel WilsonCommented:
I think the OP means something like this -- which doesn't work.  I think I've seen a way to accomplish it ... but can't remember the solution.

declare @I int
Declare @SQL varchar(max)
set @SQL = 'Select count(*) from sys.tables'
set @I = exec (@SQL)

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
David L. HansenProgrammer AnalystAuthor Commented:
Just to clarify, I want to fill a variable using a dynamic sql query.  I can build and execute the query dynamically right now....but....I want to pass the result into a variable.
0
 
Daniel WilsonCommented:
Another syntax that fails:

declare @I int
Declare @SQL varchar(max)
set @SQL = 'Select @I= count(*) from sys.tables'
exec (@SQL)

Open in new window

0
 
David L. HansenProgrammer AnalystAuthor Commented:
Yes, Daniel...that's what I want exactly!
0
 
Daniel WilsonCommented:
sl8rz, have I got the idea you're working on correct?
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Yes, but you're right.  With this approach it doesn't work (syntax).
0
 
Daniel WilsonCommented:
here it is:

http://sqlserver2000.databases.aspfaq.com/how-do-i-get-the-result-of-dynamic-sql-into-a-variable.html

select @ideclare @I int
Declare @SQL nvarchar(max)
set @SQL = 'Select @I= count(*) from sys.tables'
    EXEC sp_executesql
        @statement = @sql,
        @params = N'@i INT OUTPUT',
        @i = @i OUTPUT

select @i
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Thank you Daniel!

That's it!
0
 
David L. HansenProgrammer AnalystAuthor Commented:
Awsome work...thank you very much.
0
 
SharathData EngineerCommented:

Hope you got idea from the links provided by DanielWilson and aneeshattingal. Just rephrasing the same as there are some errors in DanielWilson  solution.

declare @I int
Declare @SQL nvarchar(max)
set @SQL = 'Select @cnt = count(*) from sys.tables'
exec sp_executesql @SQL,N'@cnt int output',@cnt = @I output
select @I

Open in new window

0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now