Solved

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

Posted on 2009-05-13
13
365 Views
Last Modified: 2012-05-06
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
Comment
Question by:David L. Hansen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24380334
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
 
LVL 4

Expert Comment

by:ruffone
ID: 24380335
Is this what your are asking?
declare @personId int
SELECT @personId = personId FROM People WHERE personId =784
 
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24380374
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 15

Author Comment

by:David L. Hansen
ID: 24380378
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24380380
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
 
LVL 15

Author Comment

by:David L. Hansen
ID: 24380383
Yes, Daniel...that's what I want exactly!
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24380384
sl8rz, have I got the idea you're working on correct?
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 24380391
Yes, but you're right.  With this approach it doesn't work (syntax).
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 24380404
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24380413
0
 
LVL 15

Author Comment

by:David L. Hansen
ID: 24380462
Thank you Daniel!

That's it!
0
 
LVL 15

Author Closing Comment

by:David L. Hansen
ID: 31581256
Awsome work...thank you very much.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24380480

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question