Sthokala
asked on
using variables in whereclause
Hi,
I have something like below in my stored procedure
IF @physicianID = 0 OR @physicianID IS NULL
BEGIN
SET @whereClause = 'requestedby=@physicianID
and examdate>=@startDate
and examdate<=@endDate'
END
ELSE
BEGIN
SET @whereClause ='examdate>=@startDate
and examdate<=@endDate'
END
and I used the variable @whereCluase like below
select
examdate,
patient_no
from exams
where @whereClause
group by examdate,patient_no
Can I do like this? or please let me know how can I do that .
Thank you
I have something like below in my stored procedure
IF @physicianID = 0 OR @physicianID IS NULL
BEGIN
SET @whereClause = 'requestedby=@physicianID
and examdate>=@startDate
and examdate<=@endDate'
END
ELSE
BEGIN
SET @whereClause ='examdate>=@startDate
and examdate<=@endDate'
END
and I used the variable @whereCluase like below
select
examdate,
patient_no
from exams
where @whereClause
group by examdate,patient_no
Can I do like this? or please let me know how can I do that .
Thank you
oops, you need to work on this part too
SET @whereClause = 'requestedby=@physicianID
and examdate>=@startDate
and examdate<=@endDate'
>>>>
SET @whereClause = 'requestedby=' + @physicianID +
' and examdate>= ''' + @startDate + ''''
' and examdate<= ''' + @endDate + ''''
SET @whereClause = 'requestedby=@physicianID
and examdate>=@startDate
and examdate<=@endDate'
>>>>
SET @whereClause = 'requestedby=' + @physicianID +
' and examdate>= ''' + @startDate + ''''
' and examdate<= ''' + @endDate + ''''
and I guess there is logical error in your code "if... then ... else..." logic should be reversed...
so, final structure should be something like this
so, final structure should be something like this
declare @whereClause varchar(max)
declare @startDate varchar(12)
declare @endDate varchar(12)
declare @physicianID int
IF isNull(@physicianID,0) = 0
BEGIN
SET @whereClause ='examdate >= ''' + @startDate + ''' and examdate <= ''' + @endDate + ''''
END
ELSE
BEGIN
SET @whereClause = 'requestedby=' + @physicianID + ' and examdate>= ''' + @startDate + ''' and examdate<= ''' + @endDate + ''''
END
declare @sql varchar(max) = 'select ... from ...' + @whereClause
exec @sql
You have two choices:
1. Use one query, which tests the variable and column as needed.
This may not produce an optimal query plan every time it run.
2. Use dynamic SQL, which will produce an optimal query plan, but is more work
to set up **and requires specific authorities being granted *directly* to the id
used to run the SQL code**.
--1.
SELECT
examdate,
patient_no
FROM dbo.exams
WHERE
(@physicianID = 0 OR @physicianID IS NULL OR requestedby = @physicianID) AND
examdate>=@startDate and
examdate<=@endDate'
GROUP BY
examdate, patient_no
1. Use one query, which tests the variable and column as needed.
This may not produce an optimal query plan every time it run.
2. Use dynamic SQL, which will produce an optimal query plan, but is more work
to set up **and requires specific authorities being granted *directly* to the id
used to run the SQL code**.
--1.
SELECT
examdate,
patient_no
FROM dbo.exams
WHERE
(@physicianID = 0 OR @physicianID IS NULL OR requestedby = @physicianID) AND
examdate>=@startDate and
examdate<=@endDate'
GROUP BY
examdate, patient_no
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Thank you for your comments. I tried below way
DECLARE @sql nvarchar(max)
SET @sql = ' INSERT INTO #TmpMUStatus
SELECT count(*), (SELECT name from users where user_no='+CAST(@physicianI D AS varchar(10))+') as physicianName,null,null,nu ll,null,nu ll,null,nu ll,null,nu ll,null,NU LL,NULL from(
SELECT
examdate,
patient_no
FROM exams WHERE
examdate>='+LEFT(CONVERT(V ARCHAR, @startDate, 120), 10)
+' AND
examdate<='+LEFT(CONVERT(V ARCHAR, @endDate, 120), 10)
IF @physicianID > 0
SET @sql = @sql + ' AND
requestedby = ' + CAST(@physicianID AS varchar(10))
SET @sql = @sql + '
GROUP BY examdate,patient_no) AS SourceQuery'
EXEC @sql
I am getting below error
Msg 203, Level 16, State 2, Procedure GenerateMUStats, Line 54
The name ' INSERT INTO #TmpMUStatus
SELECT count(*), (SELECT name from users where user_no=73408) as physicianName,null,null,nu ll,null,nu ll,null,nu ll,null,nu ll,null,NU LL,NULL from(
SELECT
examdate,
patient_no
FROM exams WHERE
examdate>=2012-01-01 AND
examdate<=2012-05-05 AND
requestedby = 73408
GROUP BY examdate,patient_no) AS SourceQuery' is not a valid identifier.
Thank you for your comments. I tried below way
DECLARE @sql nvarchar(max)
SET @sql = ' INSERT INTO #TmpMUStatus
SELECT count(*), (SELECT name from users where user_no='+CAST(@physicianI
SELECT
examdate,
patient_no
FROM exams WHERE
examdate>='+LEFT(CONVERT(V
+' AND
examdate<='+LEFT(CONVERT(V
IF @physicianID > 0
SET @sql = @sql + ' AND
requestedby = ' + CAST(@physicianID AS varchar(10))
SET @sql = @sql + '
GROUP BY examdate,patient_no) AS SourceQuery'
EXEC @sql
I am getting below error
Msg 203, Level 16, State 2, Procedure GenerateMUStats, Line 54
The name ' INSERT INTO #TmpMUStatus
SELECT count(*), (SELECT name from users where user_no=73408) as physicianName,null,null,nu
SELECT
examdate,
patient_no
FROM exams WHERE
examdate>=2012-01-01 AND
examdate<=2012-05-05 AND
requestedby = 73408
GROUP BY examdate,patient_no) AS SourceQuery' is not a valid identifier.
EXEC (@sql) -- the parens are required
Since you want pass to the date values in, you also need additional quotes:
...
FROM exams WHERE
examdate>='''+LEFT(CONVERT (VARCHAR, @startDate, 112), 8)
+''' AND
examdate<='''+LEFT(CONVERT (VARCHAR, @endDate, 112), 8) + ''''
...
Since you want pass to the date values in, you also need additional quotes:
...
FROM exams WHERE
examdate>='''+LEFT(CONVERT
+''' AND
examdate<='''+LEFT(CONVERT
...
declare @sql varchar(max) = 'select ... from ...' + @whereClause
exec @sql