Sp with Subquery/where statement
-
Hello all:
I'm looking for a little guidance with the stored procedure I have written below. I'm not sure if I have a syntax error or the code is incorrect. Should I be using a sub query? Is there a better way? I'm pretty new to T-SQL.
Any help is greatly appreciated.
CREATE PROCEDURE dbo.sp_ScriptsWith
(
@Product VARCHAR(20),
@TestPlan VARCHAR(30)
)
AS
BEGIN
DECLARE @sqlStmt AS NVARCHAR (4000)
DECLARE @crntProd AS NVARCHAR(20)
DECLARE @Tplan AS NVARCHAR(50)
SET @crntProd = CAST(@Product AS NVARCHAR(20))
SET @Tplan = CAST(@TestPlan AS NVARCHAR(30))
SET @sqlStmt = N'SELECT * FROM (SELECT tbl' + @crntProd + 'modelFinal.lngModelOfficeNum, tbl'
+ @crntProd + 'PolicyNumbers.lngPolicyNumber AS lngPolicyNumber,
tblConditions.lngConditionNumber AS lngConditionNumber,
tblConditions.numCycle AS numCycle,
tblConditions.strTestPlan AS TplanCond,
..........
..........
FROM dbo.tblConditions RIGHT OUTER JOIN
dbo.tbl' + @crntProd + 'PolicyNumbers ON
dbo.tblConditions.lngModelOfficeNum = dbo.tbl' +@crntProd +
'PolicyNumbers.lngModelOfficeNum
RIGHT OUTER JOIN
dbo.tbl' +@crntProd + 'modelFinal ON
dbo.tbl' +@crntProd + 'PolicyNumbers.lngModelOfficeNum = dbo.tbl'
+ @crntProd + 'modelFinal.lngModelOfficeNum)p
WHERE p.TplanCond=' + @Tplan
EXEC sp_executesql @sqlStmt
END
GO
-
Hi,
First I am unclear what you arre trying to do. It appears from this script:
SELECT * FROM ( SELECT tblthisproductmodelFinal.lngModelOfficeNum, tblthisproductPolicyNumbers.lngPolicyNumber AS lngPolicyNumber, tblConditions.lngConditionNumber AS lngConditionNumber, tblConditions.numCycle AS numCycle, tblConditions.strTestPlan AS TplanCond FROM dbo.tblConditions RIGHT OUTER JOIN dbo.tblthisproductPolicyNumbers ON dbo.tblConditions.lngModelOfficeNum = dbo.tblthisproductPolicyNumbers.lngModelOfficeNum RIGHT OUTER JOIN dbo.tblthisproductmodelFinal ON dbo.tblthisproductPolicyNumbers.lngModelOfficeNum = dbo.tblthisproductmodelFinal.lngModelOfficeNum ) p WHERE p.TplanCond=thistestplan
That you have tables called Conditions, tblproductproductmodel, tblproductpolicynumbers and you are joining them on lngofficemodelnumber.... I am unclear why you are wrapping the whole thing into another query.
Could You please expand on what you are trying to do?
thanks
Tal McMahon
-
Tal:
Thanks for your reply. You understand the script correctly. The main problem I'm having is the WHERE statement. It doesn't seem to work wherever I put it. With the above code, I get an 'invalid column' error. I get the same error when I throw the WHERE in the SQLstmt without wrapping it in a query. I guess I'm just trying to figure out the best way to pass a variable into the WHERE statement for the above query.
Thanks,
Cleech
-
You're missing some quotes
....WHERE p.TplanCond='thistestplan'
Without the quotes sql is looking for a column named thistestplan, which I assume from your comment doesn't exist.
Replace the where clause in the string you're building up with the following
WHERE p.TplanCond=''' + @Tplan ''''
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
That said, I don't think you need the nested SQL statement.
SELECT tblthisproductmodelFinal.lngModelOfficeNum,
tblthisproductPolicyNumbers.lngPolicyNumber AS lngPolicyNumber,
tblConditions.lngConditionNumber AS lngConditionNumber,
tblConditions.numCycle AS numCycle,
tblConditions.strTestPlan AS TplanCond
FROM dbo.tblConditions
RIGHT OUTER JOIN dbo.tblthisproductPolicyNumbers ON dbo.tblConditions.lngModelOfficeNum = dbo.tblthisproductPolicyNumbers.lngModelOfficeNum
RIGHT OUTER JOIN dbo.tblthisproductmodelFinal ON dbo.tblthisproductPolicyNumbers.lngModelOfficeNum = dbo.tblthisproductmodelFinal.lngModelOfficeNum
WHERE TplanCond='thistestplan'
should work just fine
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks for all the help so far. I'm still having trouble getting single quotation marks around the Tplan variable. It's driving me bonkos. I tried the syntax you suggested for the WHERE statement and get -- Incorrect syntax near '''
The SQL statement works fine on it's own and all of the variables are passed correctly. It's just getting the quotes around the @Tplan variable. I think it has got to be something with this long concatenated string.
Here is the code as it stands now:
ALTER PROCEDURE dbo.sp_ScriptsWith
(
@Product VARCHAR(20),
@TestPlan VARCHAR(30)
)
AS
BEGIN
DECLARE @sqlStmt AS NVARCHAR (4000)
DECLARE @crntProd AS NVARCHAR(20)
DECLARE @Tplan AS NVARCHAR(50)
SET @crntProd = CAST(@Product AS NVARCHAR(20))
SET @Tplan = CAST(@TestPlan AS NVARCHAR(30))
SET @sqlStmt = N'SELECT tbl' + @crntProd + 'modelFinal.lngModelOfficeNum, tbl'
+ @crntProd + 'PolicyNumbers.lngPolicyNumber AS lngPolicyNumber,
tblConditions.lngConditionNumber AS lngConditionNumber,
tblConditions.strUser AS strUser,
tblConditions.numCycle AS numCycle,
tblConditions.strTestPlan AS TplanCond,
.............
..............
FROM dbo.tblConditions RIGHT OUTER JOIN
dbo.tbl' + @crntProd + 'PolicyNumbers ON
dbo.tblConditions.lngModelOfficeNum = dbo.tbl' +@crntProd +
'PolicyNumbers.lngModelOfficeNum
RIGHT OUTER JOIN
dbo.tbl' +@crntProd + 'modelFinal ON
dbo.tbl' +@crntProd + 'PolicyNumbers.lngModelOfficeNum = dbo.tbl'
+ @crntProd + 'modelFinal.lngModelOfficeNum
WHERE TplanCond=''' + @Tplan ''''
PRINT @sqlstmt
EXEC sp_executesql @sqlStmt
END
GO
-
Sorry for the delay,
I agree you do not need the subquery.
As for the single double triple quadruple quotes thing, I found it helped out a lot when I started using the QUOTENAME function it puts the quotes in the right place for you so you do not have to fight with all of the quotes here is a typical statement I use:
SET @Input='EXECUTE Audit ' +
CAST(@fk_User AS VARCHAR) +
',' +
QUOTENAME(QUOTENAME(CAST(@pk_Report AS CHAR(36)),'{'),'''') +
',' +
QUOTENAME(@fk_ReportType,'''') +
',' +
QUOTENAME('UPDATE','''')
EXECUTE(@Input)
it seems to make it easier than all of the in out thinking
just a thought
tal mcmahon
-
It's actually quite simple.
inside a string two single quotes next to each other embeds on quote in the string, therefore:
'It''s going to rain on Michael''s head today'
would create the following string value
It's going to rain on Michael's head today
So if you ever see three single quotes in a row you are looking at either a single quote at the begining of a string or at the end of a string as in:
'To quote; ''Be not afraid'''
which gives us the following string
To quote; 'Be not afraid'
Pretty simple really. I would of course prefer to see you not using inline sql.
Michael R. Schmidt
Developer -
michael,
I fully inderstand how to use the single quotes and double quotes, Cleech however had missed a few in his code and said that he was having problems. I suggested useing QUOTENAME function as it eliminates some of the weirdness of statements like
SET @Input='
EXECUTE Audit ' +
CAST(@fk_User AS VARCHAR) +
',''{'+
CAST(@pk_Report AS CHAR(36)) +
'}'',''' +
fk_ReportType +
''',''UPDATE'''
as for the inline SQL, how would you suggest someone query against a table thats name is parameterized other than with dynamic sql?
Dynamic SQL is a valid and useful toof for many types of queries
tal
-
Yes, dynamic SQL is useful, I just dont like it. Sometimes it is a matter of table design that can remove the need to query using a dynamic table name like this. If you look at this query it might - and I say might since I dont have access to the schema - be able to be better designed as a join, which could be accomlished by storing the data in one table with a "type" attribute column which would accomplish the same thing as the dynamic table name in this instance.
Dynamic SQL requires direct access tot he tables which leaves possible holes for sql injection attacks. I prefer to give my users access to SPs only when possible.
In my quote examples I was attempting to simplify the "rules" for quotes for the original poster. If I came accross in any way other than that I appologize. I know that I often come accross much more abbreviated in my replies than I do in person.
Michael R. Schmidt
Developer -
Mikey,
NP i understand how posts are misinterpreted. I never allow SQL queries against my data from the client.
I guess I always think that every query is inside of a Stored procedure. so using dynamic SQL only allows the user to access what I have dynamically built.
in my current project I have like 50 tables that are used to populate drop down controls and to build an Insert, Update, Select, and Delete procedure for all of them is too much work in the event of changes. (read change one proc not 50)
instead I have a DictionaryInsert, DictionaryUpdate, etc.....that have the tablename as a parameter that the application knows and sets the user never has the opportunity as the code behind is the only place that the procedure is setting the parameter.
I sometimes forget that there are people building queries in their asp or whatever that are not using stored procs.
ymmv
Tal
-
I do most heartily apologise, I missed out a + when I specified the where clause for you.
It should be
WHERE TplanCond=''' + @Tplan + ''''
*most embarrassed*
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply