RIAS
asked on
View Sql in stored procedure
Hello,
How can I view this sql in the stored procedure
Setting
DECLARE @SQL NVARCHAR(MAX)
SET @SQL ='
allows me to view the sql .
Any suggestion on the above query
How can I view this sql in the stored procedure
IF NOT EXISTS( SELECT Ref
FROM [CLIENT_REFERENCE] WHERE(
([OrgAuth] = @OrgAuth OR
([OrgAuth] is null AND @OrgAuth is null) OR
([OrgAuth] is null AND @OrgAuth ='') OR
([OrgAuth] ='' AND @OrgAuth is null))
AND
([ClientName] = @ClientName OR
([ClientName] is null AND @ClientName is null) OR
([ClientName] is null AND @ClientName ='') OR
([ClientName] ='' AND @ClientName is null))
AND
([Sex] = @Sex OR
([Sex] is null AND @Sex is null) OR
([Sex] is null AND @Sex ='') OR
([Sex] ='' AND @Sex is null))
AND
([Comments] = @Comments OR
([Comments] is null AND @Comments is null) OR
([Comments] is null AND @Comments ='') OR
([Comments] ='' AND @Comments is null))
AND
([Field1] = @Field1 OR
([Field1] is null AND @Field1 is null) OR
([Field1] is null AND @Field1 ='') OR
([Field1] ='' AND @Field1 is null))))
Setting
DECLARE @SQL NVARCHAR(MAX)
SET @SQL ='
allows me to view the sql .
Any suggestion on the above query
ASKER
Need to see the query when I debug the stored procedure.
Sql = "If exists ..."
When you set the query in set sql = we can view it or else there is no way we can debug it.
How can i set the above query as set sql =
Sql = "If exists ..."
When you set the query in set sql = we can view it or else there is no way we can debug it.
How can i set the above query as set sql =
Still not sure if I got it.
If you run the command PRINT @SQL it will show you the query.
If you run the command PRINT @SQL it will show you the query.
ASKER
Yes agreed but , how do I print this query ?
IF NOT EXISTS( SELECT Ref
FROM [CLIENT_REFERENCE] WHERE(
([OrgAuth] = @OrgAuth OR
([OrgAuth] is null AND @OrgAuth is null) OR
([OrgAuth] is null AND @OrgAuth ='') OR
([OrgAuth] ='' AND @OrgAuth is null))
AND
([ClientName] = @ClientName OR
([ClientName] is null AND @ClientName is null) OR
([ClientName] is null AND @ClientName ='') OR
([ClientName] ='' AND @ClientName is null))
AND
([Sex] = @Sex OR
([Sex] is null AND @Sex is null) OR
([Sex] is null AND @Sex ='') OR
([Sex] ='' AND @Sex is null))
AND
([Comments] = @Comments OR
([Comments] is null AND @Comments is null) OR
([Comments] is null AND @Comments ='') OR
([Comments] ='' AND @Comments is null))
AND
([Field1] = @Field1 OR
([Field1] is null AND @Field1 is null) OR
([Field1] is null AND @Field1 ='') OR
([Field1] ='' AND @Field1 is null))))
ASKER
Entire Sp is
ALTER PROCEDURE [dbo].[TL_AdClieference]
@Ref nvarchar(50) = NULL, @OrgAuth nvarchar(3) = NULL, @ClientName nvarchar(255) = NULL,
@Sex [nvarchar](1) = NULL, @Comments nvarchar(max)= NULL, @Field1 nvarchar(50)= NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF NOT EXISTS( SELECT Ref
FROM [CLIENT_REFERENCE] WHERE(
([OrgAuth] = @OrgAuth OR
([OrgAuth] is null AND @OrgAuth is null) OR
([OrgAuth] is null AND @OrgAuth ='') OR
([OrgAuth] ='' AND @OrgAuth is null))
AND
([ClientName] = @ClientName OR
([ClientName] is null AND @ClientName is null) OR
([ClientName] is null AND @ClientName ='') OR
([ClientName] ='' AND @ClientName is null))
AND
([Sex] = @Sex OR
([Sex] is null AND @Sex is null) OR
([Sex] is null AND @Sex ='') OR
([Sex] ='' AND @Sex is null))
AND
([Comments] = @Comments OR
([Comments] is null AND @Comments is null) OR
([Comments] is null AND @Comments ='') OR
([Comments] ='' AND @Comments is null))
AND
([Field1] = @Field1 OR
([Field1] is null AND @Field1 is null) OR
([Field1] is null AND @Field1 ='') OR
([Field1] ='' AND @Field1 is null))))
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SET @SQL =' INSERT INTO [dbo].[CLIENT_REFERENCE] ( '
IF @Ref <> ''
SET @SQL = @SQL + ' [Ref] '
IF @OrgAuth <> ''
SET @SQL =@SQL + ',' + ' [OrgAuth] '
IF @ClientName <> ''
SET @SQL =@SQL + ',' + ' [ClientName] '
IF @Sex <> ''
SET @SQL =@SQL + ',' + ' [Sex] '
IF @Comments <> ''
SET @SQL =@SQL + ',' + ' [Comments] '
IF @Field1 <> ''
SET @SQL =@SQL + ',' + ' [Field1] '
SET @SQL = @SQL +')' + '
VALUES
('
IF @Ref <> ''
SET @SQL =@SQL + '''' + CAST(@Ref AS Nvarchar(50)) + ''''
IF @OrgAuth <> ''
SET @SQL = @SQL + ' , ''' + CAST(@OrgAuth AS Nvarchar(255)) + ''''
IF @ClientName <> ''
SET @SQL = @SQL + ' , ''' + CAST(@ClientName AS Nvarchar(20)) + ''''
IF @Sex <> ''
SET @SQL = @SQL + ' , ''' + CAST(@Sex AS Nvarchar(1)) + ''''
IF @Comments <> ''
SET @SQL = @SQL + ' , ''' + CAST(@Comments AS Nvarchar(50)) + ''''
IF @Field1 <> ''
SET @SQL = @SQL + ' , ''' + CAST(@Field1 AS Nvarchar(50)) + ''''
SET @SQL = @SQL + ')'
EXECUTE (@SQL)
INSERT INTO TRAVEL_DEBUG(SQL) VALUES (@SQL)
Return @@Rowcount
END
ELSE
BEGIN
Return 2
END
END
ASKER
need to print or view
IF NOT EXISTS( SELECT Ref
FROM [CLIENT_REFERENCE] WHERE(
([OrgAuth] = @OrgAuth OR
([OrgAuth] is null AND @OrgAuth is null) OR
([OrgAuth] is null AND @OrgAuth ='') OR
([OrgAuth] ='' AND @OrgAuth is null))
AND
([ClientName] = @ClientName OR
([ClientName] is null AND @ClientName is null) OR
([ClientName] is null AND @ClientName ='') OR
([ClientName] ='' AND @ClientName is null))
AND
([Sex] = @Sex OR
([Sex] is null AND @Sex is null) OR
([Sex] is null AND @Sex ='') OR
([Sex] ='' AND @Sex is null))
AND
([Comments] = @Comments OR
([Comments] is null AND @Comments is null) OR
([Comments] is null AND @Comments ='') OR
([Comments] ='' AND @Comments is null))
AND
([Field1] = @Field1 OR
([Field1] is null AND @Field1 is null) OR
([Field1] is null AND @Field1 ='') OR
([Field1] ='' AND @Field1 is null))))
You can't print a query by itself.
However you can print a string and that's what you do when you deal with dynamic queries because you built it with a string variable and then execute the string content that's a T-SQL command.
However you can print a string and that's what you do when you deal with dynamic queries because you built it with a string variable and then execute the string content that's a T-SQL command.
ASKER
Thats the question Vitor ,
How can I set this query to string?
How can I set this query to string?
ASKER
example :
Set sql = IF NOT EXISTS( SELECT Ref
FROM [CLIENT_REFERENCE] WHERE(
([OrgAuth] = @OrgAuth OR
([OrgAuth] is null AND @OrgAuth is null) OR
([OrgAuth] is null AND @OrgAuth ='') OR
([OrgAuth] ='' AND @OrgAuth is null))
AND
([ClientName] = @ClientName OR
([ClientName] is null AND @ClientName is null) OR
([ClientName] is null AND @ClientName ='') OR
([ClientName] ='' AND @ClientName is null))
AND
([Sex] = @Sex OR
([Sex] is null AND @Sex is null) OR
([Sex] is null AND @Sex ='') OR
([Sex] ='' AND @Sex is null))
AND
([Comments] = @Comments OR
([Comments] is null AND @Comments is null) OR
([Comments] is null AND @Comments ='') OR
([Comments] ='' AND @Comments is null))
AND
([Field1] = @Field1 OR
([Field1] is null AND @Field1 is null) OR
([Field1] is null AND @Field1 ='') OR
([Field1] ='' AND @Field1 is null))))
Set sql = IF NOT EXISTS( SELECT Ref
FROM [CLIENT_REFERENCE] WHERE(
([OrgAuth] = @OrgAuth OR
([OrgAuth] is null AND @OrgAuth is null) OR
([OrgAuth] is null AND @OrgAuth ='') OR
([OrgAuth] ='' AND @OrgAuth is null))
AND
([ClientName] = @ClientName OR
([ClientName] is null AND @ClientName is null) OR
([ClientName] is null AND @ClientName ='') OR
([ClientName] ='' AND @ClientName is null))
AND
([Sex] = @Sex OR
([Sex] is null AND @Sex is null) OR
([Sex] is null AND @Sex ='') OR
([Sex] ='' AND @Sex is null))
AND
([Comments] = @Comments OR
([Comments] is null AND @Comments is null) OR
([Comments] is null AND @Comments ='') OR
([Comments] ='' AND @Comments is null))
AND
([Field1] = @Field1 OR
([Field1] is null AND @Field1 is null) OR
([Field1] is null AND @Field1 ='') OR
([Field1] ='' AND @Field1 is null))))
Ok, got it.
The following should work assuming that the variables are all strings:
The following should work assuming that the variables are all strings:
DECLARE @SQL NVARCHAR(MAX)
SET @SQL ='
IF NOT EXISTS( SELECT Ref
FROM [CLIENT_REFERENCE]
WHERE (
([OrgAuth] = ' + @OrgAuth + ' OR
([OrgAuth] is null AND ' + @OrgAuth + ' is null) OR
([OrgAuth] is null AND ' + @OrgAuth + ' ='''') OR
([OrgAuth] ='''' AND ' + @OrgAuth + 'is null))
AND
([ClientName] = ' + @ClientName + ' OR
([ClientName] is null AND ' + @ClientName + ' is null) OR
([ClientName] is null AND ' + @ClientName + ' ='''') OR
([ClientName] ='''' AND ' + @ClientName + ' is null))
AND
([Sex] = ' + @Sex + ' OR
([Sex] is null AND ' + @Sex + ' is null) OR
([Sex] is null AND ' + @Sex + ' ='''') OR
([Sex] ='''' AND ' + @Sex + ' is null))
AND
([Comments] = ' + @Comments + ' OR
([Comments] is null AND ' + @Comments + ' is null) OR
([Comments] is null AND ' + @Comments + ' ='''') OR
([Comments] ='''' AND ' + @Comments + ' is null))
AND
([Field1] = ' + @Field1 + ' OR
([Field1] is null AND ' + @Field1 + ' is null) OR
([Field1] is null AND ' + @Field1 + ' ='''') OR
([Field1] ='''' AND ' + @Field1 + ' is null))))'
PRINT @SQL
ASKER
Cool! Will try now and get back
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks ,minor syntax issues but,will fix it.Thanks for the solution
Can you rephrase please?