Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Return a single value to a variable

Posted on 2006-07-07
2
Medium Priority
?
837 Views
Last Modified: 2012-08-13
I'm trying to get a single value (row count) and write it to a text file.

I'm trying to get the row count value into the variable @spcount and then output it using this code:
set @cmd = 'master..xp_cmdshell "echo Record Count:' + @spcount + '>> d:\file.txt"'
exec (@cmd)

Unfortunately, I can't seem to pass the row count value via the @spcount variable to the file, I always get a value of 0

the code I'm trying to use to assign the value is:
exec @spcount=xdb..sp_executesql N'select count(*) from xtable'

another variation of that line:
exec xdb..sp_executesql N'select count(*) from xtable'

run by themselves, I get the correct output, but when trying to embed the results into the code above, again I get 0

any suggestions?



0
Comment
Question by:wppiexperts
  • 2
2 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17060614
this should work better:
select @spcount=count(*) from xtable
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 17060628
resp this:

exec xdb..sp_executesql N'select @res = count(*) from xtable',  N'@res int output'  , @spcount output
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

885 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