Getting output value from dynamically executed query ?
-
Hai.....,
I have a stored procedure that takes 4 parameters. All the parameters hold the default value NULL. What i want is to build the query based on the NON-NULL parameters and get the row count for that dynamic query. I 'm really confused as how to accomplish this.
Below, I have posted few lines of that stored proc that can help you to understand my problem clearly.
DECLARE @CustomerCode VARCHAR(50),
@DiscontinuedPart VARCHAR(50),
@FromDate SMALLDATETIME,
@ToDate SMALLDATETIME
DECLARE @CNT INT
DECLARE @Query VARCHAR(8000)
DECLARE @Param VARCHAR(8000)
DECLARE @Values VARCHAR
SET @CustomerCode = 'c001'
SET @DiscontinuedPart = NULL
SET @FromDate = NULL
SET @ToDate = NULL
SET @Query = 'SELECT @Cnt = Count(*) FROM tblServiceWarningLog WHERE 1 = 1'
IF @CustomerCode IS NOT NULL
BEGIN
SET @Query = @Query + ' AND CustomerCode = @CustomerCode '
SET @Param = '@CustomerCode CustomerCode'
END
IF @DiscontinuedPart IS NOT NULL
SET @Query = @Query + ' AND DiscontinuedPart = @DiscontinuedPart '
IF @FromDate IS NOT NULL AND @ToDate IS NOT NULL
SET @Query = @Query + ' AND WarningDateTime BETWEEN @FromDate AND @ToDate '
IF @FromDate IS NOT NULL
SET @Query = @Query + ' AND YEAR(WarningDateTime) = YEAR(@FromDate) AND MONTH(WarningDateTime) = MONTH(@FromDate) AND DAY(WarningDateTime) = DAY(@FromDate)'
Please help me to return the record count for the dynamically generated query
-
Hi, check out this by running it on Northwind dbase, m sure u can get the rest as per your needs
use northwind
declare @cnt int
, @cname nvarchar(40)
declare @sql nvarchar(4000)
set @cname = 'A%'
select @sql = 'select @xcnt = count(*) from customers where companyname like @xCname'
declare @paramlist nvarchar(4000)
select @paramlist = ' @xcnt int output
,@xCname nvarchar(40)'
exec sp_executesql @sql, @paramlist, @cnt output, @cname
select @cnt
Regards,
Peldin Fernandes -
Hai Peldin,
I think you didn't get my question. OK i will take your example and make the question clear. In your example, you have used a variable named cname and given a value as 'A%'. OK this would return as expected.
The next thing is that I 'm allowing NULL values to the cname in the query. In this situation I want the following query to be executed:
select @sql = 'select @xcnt = count(*) from customers '
I know that you will be thinking of IF .... ELSE ....block. But my stored procedure has 4 parameters which are capable of accepting NULL values. And here, if I adopt this technique I would end up with 4! = 24 IF ELSE blocks which is really a bad thing.
Hope, now you got my problem and expecting a solution for this
-
Hello Everyone,
I have adapted the following method to put the values directly into the query instead of supplying that as a seperate parameter list. Anyways thanx everyone for helping me solve the problem.
DECLARE @CustomerCode VARCHAR(50), @DiscontinuedPart VARCHAR(50)
DECLARE @CNT INT
DECLARE @Query NVARCHAR(4000)
DECLARE @Param NVARCHAR(4000)
SET @CustomerCode = 'c001'
SET @DiscontinuedPart = NULL
SET @Query = 'SELECT @Cnt = Count(*) FROM tblServiceWarningLog WHERE 1 = 1'
SET @Param = ' @Cnt int output'
IF @CustomerCode IS NOT NULL
SET @Query = @Query + ' AND CustomerCode = ''' + @CustomerCode + ''''
IF @DiscontinuedPart IS NOT NULL
SET @Query = @Query + ' AND DiscontinuedPart = ''' + @DiscontinuedPart + ''''
EXEC sp_executesql @Query, @Param, @CNT output
SELECT @Cnt
-
Hemant,
!!! ahaRegards,
Peldin Fernandes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply