Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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