April 20, 2019 at 5:18 am
I want to print the SQL Query which is used in the below stored procedure with parameter values.Currently it is printing the SQL Query without parameter values substitution while executing the stored procedure.It is printing the the parameters as it is(for example AND TP.F_LANGUAGE = @LANGUAGE)parameter value is not substituted.
CREATE PROCEDURE [dbo].[SEARCH]
@LANGUAGE VARCHAR(2),
@SUBFORMAT NVARCHAR(2000),
@ico NVARCHAR(4000),
AS
SET NOCOUNT ON
DECLARE @sqlquery AS NVARCHAR(MAX)
DECLARE @pdfQuery AS NVARCHAR(MAX)
DECLARE @htmlQuery AS NVARCHAR(MAX)
DECLARE @param AS NVARCHAR(3000)
DECLARE @paramDefinition AS NVARCHAR(3000)
Declare @AllSubformats AS SubformatType
Declare @InputSubformats AS SubformatType
INSERT INTO @AllSubformats(Val)
SELECT S.Val
FROM (SELECT IsNull(TLK.f_value, '') As FValue
FROM T_LOOKUP TLK
WHERE TLK.f_parent = 'WEBVIEWER_INT_SUB'
) TLV
CROSS APPLY dbo.dfnSplit(TLV.FValue, ',') S
;
If (@SUBFORMAT <> '-1')
Begin
INSERT INTO @InputSubformats(Val)
SELECT S.Val
FROM dbo.dfnSplit(@SUBFORMAT, ',') S
End
;
SET @pdfQuery = 'SELECT TOP 1001 TP.F_PRODUCT AS ID,
TP.F_PRODUCT_NAME AS NAME,
FROM PDF_DETAILS TP '
WHERE TP.F_PRODUCT<>'''''
SET @param = ' AND TP.F_AUTHORIZED IN(1,3) AND EXISTS (SELECT 1 FROM @AllSubformats ASF WHERE ASF.Val = TP.F_SUBFORMAT)'
IF NOT(@LANGUAGE IS NULL OR @LANGUAGE = '')
SET @param = @param + ' AND TP.F_LANGUAGE = @LANGUAGE '
IF NOT(@SUBFORMAT IS NULL OR @SUBFORMAT = '')
SET @param = @param + ' AND EXISTS (SELECT 1 FROM @InputSubformats ISF WHERE ISF.Val = TP.F_SUBFORMAT) '
IF NOT(@ICO IS NULL OR @ico = '')
SET @param = @param + ' AND (TP.F_ICO_DATA LIKE @ico) '
SET @ParamDefinition = ' @SUBFORMAT NVARCHAR(2000),
@LANGUAGE VARCHAR(2), @ico NVARCHAR(4000),@AllSubformats SubformatType READONLY, @InputSubformats SubformatType READONLY '
SET @sqlquery = @pdfQuery + @param
EXECUTE SP_EXECUTESQL @sqlquery, @paramDefinition, @SUBFORMAT, @LANGUAGE, @ico, @AllSubformats, @InputSubformats
Print @sqlquery
Please help.I want to debug and execute the SQL QUERY.
April 20, 2019 at 10:14 am
That statement looks malformed to me. If we format and correctly quote your strings we get the following (I've added some comments where there are problems):
CREATE PROCEDURE [dbo].[SEARCH]
@LANGUAGE VARCHAR(2),
@SUBFORMAT NVARCHAR(2000),
@Ico NVARCHAR(4000), --Comma but no more parameters
AS
SET NOCOUNT ON
DECLARE @sqlquery AS NVARCHAR(MAX)
DECLARE @pdfQuery AS NVARCHAR(MAX)
DECLARE @htmlQuery AS NVARCHAR(MAX)
DECLARE @param AS NVARCHAR(3000)
DECLARE @paramDefinition AS NVARCHAR(3000)
Declare @AllSubformats AS SubformatType
Declare @InputSubformats AS SubformatType
INSERT INTO @AllSubformats(Val)
SELECT S.Val
FROM (SELECT IsNull(TLK.f_value, '') As FValue
FROM T_LOOKUP TLK
WHERE TLK.f_parent = 'WEBVIEWER_INT_SUB') TLV
CROSS APPLY dbo.dfnSplit(TLV.FValue, ',') S;
If (@SUBFORMAT <> '-1')
Begin
INSERT INTO @InputSubformats(Val)
SELECT S.Val
FROM dbo.dfnSplit(@SUBFORMAT, ',') S
End;
SET @pdfQuery = '
SELECT TOP 1001 TP.F_PRODUCT AS ID,
TP.F_PRODUCT_NAME AS NAME,
FROM PDF_DETAILS TP ' --Quote is closed here, but the statement clear continues
WHERE TP.F_PRODUCT<>''''' --So things go really wrong here
SET @param = ' AND TP.F_AUTHORIZED IN(1,3) AND EXISTS (SELECT 1 FROM @AllSubformats ASF WHERE ASF.Val = TP.F_SUBFORMAT)'
IF NOT(@LANGUAGE IS NULL OR @LANGUAGE = '')
SET @param = @param + ' AND TP.F_LANGUAGE = @LANGUAGE '
IF NOT(@SUBFORMAT IS NULL OR @SUBFORMAT = '')
SET @param = @param + ' AND EXISTS (SELECT 1 FROM @InputSubformats ISF WHERE ISF.Val = TP.F_SUBFORMAT) '
IF NOT(@ICO IS NULL OR @Ico = '')
SET @param = @param + ' AND (TP.F_ICO_DATA LIKE @Ico) '
SET @ParamDefinition = ' @SUBFORMAT NVARCHAR(2000),
@LANGUAGE VARCHAR(2), @Ico NVARCHAR(4000),@AllSubformats SubformatType READONLY, @InputSubformats SubformatType READONLY '
SET @sqlquery = @pdfQuery + @param
EXECUTE SP_EXECUTESQL @sqlquery, @paramDefinition, @SUBFORMAT, @LANGUAGE, @Ico, @AllSubformats, @InputSubformats
Print @sqlquery
What you're asking for doesn't actually make sense. What you have is a parametrised statement, and that is exactly what you do want. When you run a parametrised query (even something as simple as SELECT * FROM MyTable WHERE ID = @ID), if you inspect the SQL the value of @ID isn't replaced with the value of it; that would actually be a problem if it was for query plan reuse.
When you run query, a cached version of that query plan is stored. For 2 queries like the below, you would store 2 query plans:
SELECT *
FROM MyTable
WHERE ID = 1;
SELECT *
FROM MyTable
WHERE ID = 2;
On the other hand, the query below would have one, regardless of the value of @ID:
SELECT *
FROM MyTable
WHERE ID = @ID;
So, you could pass 1 Million different values for `@ID' and the same (cached) plan would be used, but if you were to use a literal value SQL Server would need to determine the query plan for the query for every different value. For a query like the above, that's unlikely to be a problem, but when you start getting more complex, then it makes a difference.
What you are asking for is actually a bad idea. Don't replace (inject) the values of your parameters in your dynamic statement; this actually opens you up a huge security flaw (SQL Injection). What you have done is the correct choice, and you should stick with it. Inspect the value of the variables outside of the dynamic statement. If SubformatType is a table-variable, you'll have to SELECT it, but I would be careful there, as it could break your application. If it is a table, as well, however, not sure how you would have expected that to be replaced with a literal value. 🙂
Anyway, I've fixed the syntax errors for you, and also added some extra formatting, but to the compiled and dynamic statement; which might make things easier for you in the future too.
CREATE PROCEDURE [dbo].[SEARCH]
@LANGUAGE VARCHAR(2),
@SUBFORMAT NVARCHAR(2000),
@Ico NVARCHAR(4000)
AS BEGIN
SET NOCOUNT ON;
DECLARE @sqlquery AS NVARCHAR(MAX);
DECLARE @pdfQuery AS NVARCHAR(MAX);
DECLARE @htmlQuery AS NVARCHAR(MAX);
DECLARE @param AS NVARCHAR(3000);
DECLARE @paramDefinition AS NVARCHAR(3000);
DECLARE @AllSubformats AS SubformatType;
DECLARE @InputSubformats AS SubformatType;
INSERT INTO @AllSubformats(Val)
SELECT S.Val
FROM (SELECT IsNull(TLK.f_value, '') As FValue
FROM T_LOOKUP TLK
WHERE TLK.f_parent = 'WEBVIEWER_INT_SUB') TLV
CROSS APPLY dbo.dfnSplit(TLV.FValue, ',') S;
If (@SUBFORMAT <> '-1')
Begin
INSERT INTO @InputSubformats(Val)
SELECT S.Val
FROM dbo.dfnSplit(@SUBFORMAT, ',') S; --I hope this isn't a WHILE/CURSOR solution :)
End;
SET @pdfQuery = N'SELECT TOP 1001' + NCHAR(13) + NCHAR(10) + --These are really useful as they put line breaks into the PRINT
N' TP.F_PRODUCT AS ID,' + NCHAR(13) + NCHAR(10) +
N' TP.F_PRODUCT_NAME AS NAME,' + NCHAR(13) + NCHAR(10) +
N'FROM PDF_DETAILS TP' + NCHAR(13) + NCHAR(10) +
N'WHERE TP.F_PRODUCT<>''''';
SET @param = NCHAR(13) + NCHAR(10) + N' AND TP.F_AUTHORIZED IN(1,3) AND EXISTS (SELECT 1 FROM @AllSubformats ASF WHERE ASF.Val = TP.F_SUBFORMAT)';
IF NOT(@LANGUAGE IS NULL OR @LANGUAGE = '')
SET @param = @param + NCHAR(13) + NCHAR(10) + N' AND TP.F_LANGUAGE = @LANGUAGE ';
IF NOT(@SUBFORMAT IS NULL OR @SUBFORMAT = '')
SET @param = @param + NCHAR(13) + NCHAR(10) + N' AND EXISTS (SELECT 1 FROM @InputSubformats ISF WHERE ISF.Val = TP.F_SUBFORMAT) ';
IF NOT(@ICO IS NULL OR @Ico = '')
SET @param = @param + NCHAR(13) + NCHAR(10) + N' AND (TP.F_ICO_DATA LIKE @Ico) ';
SET @ParamDefinition = N'@SUBFORMAT NVARCHAR(2000),' + NCHAR(13) + NCHAR(10) +
N'@LANGUAGE VARCHAR(2),' + NCHAR(13) + NCHAR(10) +
N'@Ico NVARCHAR(4000),' + NCHAR(13) + NCHAR(10) +
N'@AllSubformats SubformatType READONLY,' + NCHAR(13) + NCHAR(10) +
N'@InputSubformats SubformatType READONLY';
SET @sqlquery = @pdfQuery + @param + N';';
PRINT @sqlquery;
EXECUTE SP_EXECUTESQL @sqlquery, @paramDefinition, @SUBFORMAT, @LANGUAGE, @Ico, @AllSubformats, @InputSubformats;
END;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 25, 2019 at 5:32 am
ok.Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply