quote prob - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

quote prob

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-03-25 : 11:26:44

I have a stored proc that creates a view so I can pass parameters. I need to replace the 120 with the variable @MEA. I can't get my quotes right to make this work.
ALTER PROCEDURE dbo.spi_CallList
(@strAgent nchar(4), @MEA int)

AS

DECLARE @Msg varchar(255)
declare @SQL varchar(4000)

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList1')
DROP VIEW qs_CallList1

SET @SQL = 'Create view qs_CallList1 as
SELECT top 100 percent tsd_Claim.clinsnum AS [Ins#], Sum(tsd_Claim.cloutstandingamt) AS OutstndByIns
FROM (tsd_Claim LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim)
AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat))
LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID
WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''')
AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null)
AND ((tsd_Claim.clfromdos)<GetDate()-120))
GROUP BY tsd_Claim.clinsnum
ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC'
EXECUTE(@sql)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 11:33:38

[code]ALTER PROCEDURE dbo.spi_CallList
(@strAgent nchar(4), @MEA int)

AS

DECLARE @Msg varchar(255)
declare @SQL varchar(4000)

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList1')
DROP VIEW qs_CallList1

SET @SQL = 'Create view qs_CallList1 as
SELECT top 100 percent tsd_Claim.clinsnum AS [Ins#], Sum(tsd_Claim.cloutstandingamt) AS OutstndByIns
FROM (tsd_Claim LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim)
AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat))
LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID
WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''')
AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null)
AND ((tsd_Claim.clfromdos)<GetDate()-' + cast(@mea as varchar(12)) + '))
GROUP BY tsd_Claim.clinsnum
ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC'
EXECUTE(@sql)[/code]

E 12°55'05.25"
N 56°04'39.16"

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-03-25 : 11:35:56

Thank you Peso! The cast was getting me!

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-25 : 19:07:03

This is a terrible idea by the way.
a) Creating a view in a SP - what do you expect to gain by doing this? Why not just use the SQL when you need it or use the unparameterised view and apply the filter there.
b) Does this even work properly? What about when multiple users access the view while it is being changed?
c) Creating objects in SPs - almost always a bad idea. Can't you just script it?
d) ORDER BY in a view makes no sense. It has to go on the final select
e) TOP 100 PERCENT does not bodge around it in SQL 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources