Solved

How to get sp_executesql result into a variable?

Posted on 2012-03-29
2
603 Views
Last Modified: 2012-06-27
Hi,
  I have a query like below. I want to get count(*) result into a variable when I execute the sql. Please let me know how can I do it.


SET @sql = '   SELECT count(*), (SELECT name from users where user_no='+CAST(@physicianID AS varchar(10))+') as physicianName,null,null,null,null,null,null,null,null,null,null,NULL,NULL from(
      SELECT
            examdate,
            patient_no
      FROM exams WHERE
          examdate>='''+LEFT(CONVERT(VARCHAR, @startDate, 112), 8)
+''' AND
          examdate<='''+LEFT(CONVERT(VARCHAR, @endDate, 112), 8) + ''''

IF @physicianID > 0
    SET @sql = @sql + ' AND
           requestedby = ' + CAST(@physicianID AS varchar(10))

SET @sql = @sql + '
        GROUP BY examdate,patient_no) AS SourceQuery'
EXEC sp_executesql @sql

Thank you
0
Comment
Question by:Sthokala
2 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 37783832
here is a sample

declare @sql nvarchar(max) = 'select @ResultOut = 3'
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@ResultOut integer OUTPUT';

declare @result integer
EXEC dbo.sp_executesql @sql, @ParmDefinition, @ResultOut=@result output;
print 'Result = ' + cast(@result as varchar)

>>>>

Result = 3

Open in new window


more info : http://msdn.microsoft.com/en-us/library/ms188001.aspx
0
 
LVL 14

Accepted Solution

by:
nishant joshi earned 500 total points
ID: 37786008
get your count using sp_executesql

declare @yourcount int
SET @sql = 'Declare @totcount int  SET @totcount=SELECT count(*), (SELECT name from users where user_no='+CAST(@physicianID AS varchar(10))+') as physicianName,null,null,null,null,null,null,null,null,null,null,NULL,NULL from(
      SELECT
            examdate,
            patient_no
      FROM exams WHERE
          examdate>='''+LEFT(CONVERT(VARCHAR, @startDate, 112), 8)
+''' AND
          examdate<='''+LEFT(CONVERT(VARCHAR, @endDate, 112), 8) + ''''

IF @physicianID > 0
    SET @sql = @sql + ' AND
           requestedby = ' + CAST(@physicianID AS varchar(10))

SET @sql = @sql + '
        GROUP BY examdate,patient_no) AS SourceQuery'
EXEC sp_executesql @sql,'totcount int OUTPUT',@yourcount output

Open in new window


Regards,
nishant
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Incremental load example 2 52
MS SQL page split per second is high 19 95
Order by but want it in specific order 2 33
Can Unique column have more than one Null? 8 53
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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