June 3, 2003 at 8:48 pm
Thanks for all the help from everybody. But it was just Brown and 5409045121009 that came near to my query. Brown I tried to do in the way which you told me but i never got the resultset and when i took the comment from the exec command as you told me it still gave me error that first name and quick due fields are not declared even though they are declared. The suggestion from you looks something like this.
CREATE PROCEDURE proc_test
@param1 nvarchar(20) = null,
@param2 nvarchar(20) = null,
@param3 nvarchar(30) = null,
@param4 nvarchar(20) = null,
@param5 nvarchar(20) = null,
@param6 nvarchar(20) = null,
@param7 SMALLDATETIME = null,
@param8 SMALLDATETIME = null,
@param9 SMALLDATETIME = null,
@param10 SMALLDATETIME = null,
@param11 SMALLDATETIME = null,
@param12 nvarchar(20) = null
AS
BEGIN
DECLARE @where_clause varchar(8000),
@sql varchar(8000),
@return varchar(2)
SELECT @where_clause = 'WHERE 1=1',
@return = char(13)+char(10)
SELECT @where_clause = @where_clause +
CASE WHEN @param1 IS NOT NULL THEN @return+' AND Case_no# = '''+@param1+'''' ELSE '' END+
CASE WHEN @param2 IS NOT NULL THEN @return+' AND last_name = '''+@param2+'''' ELSE '' END+
CASE WHEN @param3 IS NOT NULL THEN @return+' AND [First name] = '''+@param3+'''' ELSE '' END+
CASE WHEN @param4 IS NOT NULL THEN @return+' AND Determination = '''+@param4+'''' ELSE '' END+
CASE WHEN @param5 IS NOT NULL THEN @return+' AND sender = '''+@param5+'''' ELSE '' END+
CASE WHEN @param6 IS NOT NULL THEN @return+' AND [Case Type] = '''+@param6+'''' ELSE '' END+
CASE WHEN @param7 IS NOT NULL THEN @return+' AND Date = ''+CAST(@param7 AS nvarchar(20))' ELSE '' END+
CASE WHEN @param8 IS NOT NULL THEN @return+' AND [Qwik Due] = ''+CAST(@param8 AS nvarchar(20))' ELSE '' END+
CASE WHEN @param9 IS NOT NULL THEN @return+' AND [Qwik sent date] = ''+CAST(@param9 AS nvarchar(20))' ELSE '' END+
CASE WHEN @param10 IS NOT NULL THEN @return+' AND [Final due] = ''+CAST(@param10 AS nvarchar(20))' ELSE '' END+
CASE WHEN @param11 IS NOT NULL THEN @return+' AND [Final sent date] = ''+CAST(@param11 AS nvarchar(20))' ELSE '' END+
CASE WHEN @param12 IS NOT NULL THEN @return+' AND [on hold code] = '''+@param12+'''' ELSE '' END
SELECT @sql = 'SELECT Case_no#,Last_name,[First Name],Determination,Sender,[Case Type],Date,[Qwik Due],[Qwik sent date],[Final due],[Final sent date],[on hold code] FROM Case30 '+@return+@where_clause+@return+
'UNION '+@return+
'SELECT Case_no#,Last_name,[First Name],Determination,Sender,[Case Type],Date,[Qwik Due],[Qwik sent date],[Final due],[Final sent date],[on hold code] FROM CASEOLDR'+@return+@where_clause
EXEC (@sql)
SELECT @sql
END
Can u make some changes in this as soon as possible so that i can get the resultset from the table?
June 3, 2003 at 8:55 pm
Hi Len,
I tried your way too but i am getting error that error near = sign and i also dont know how to get resultset from that. I am attaching the procedure in your way so that you can make suitable changes in it so that i can achieve the resultset from the table.
CREATE PROCEDURE proc_test8
(
@param1 nvarchar(20) = Null,
@param2 nvarchar(20) = Null,
@param3 nvarchar(20) = Null,
@param4 nvarchar(20) = Null,
@param5 nvarchar(20) = Null,
@param6 nvarchar(20) = Null,
@param7 SMALLDATETIME = Null,
@param8 SMALLDATETIME = Null,
@param9 SMALLDATETIME = Null,
@param10 SMALLDATETIME = Null,
@param11 SMALLDATETIME = Null,
@param12 nvarchar(20) = Null)
AS
BEGIN
SELECT @param1 as Case_no#,@param2 as Last_name,@param3 as [First Name],@param4 as Determination,@param5 as Sender,@param6 as [Case Type],@param7 as Date,@param8 as [Qwik Due],@param9 as [Qwik sent date],@param10 as [Final due],@param11 as [Final sent date],@param12 as [on hold code] FROM Case30
where
(CASE WHEN @param1 IS NOT NULL THEN Case_no# = '@param1' else '' End) and
(CASE WHEN @param2 IS NOT NULL THEN Last_name = '@param2' else '' End) and
(CASE WHEN @param3 IS NOT NULL THEN [First name] = '@param3' else '' End) and
(CASE WHEN @param4 IS NOT NULL THEN Determination = '@param4' else '' End) and
(CASE WHEN @param5 IS NOT NULL THEN Sender = '@param5' else '' End) and
(CASE WHEN @param6 IS NOT NULL THEN [Case Type] = '@param6' else '' End) and
(CASE WHEN @param7 IS NOT NULL THEN Date = 'CAST(@param7 AS nvarchar(20))'else '' End) and
(CASE WHEN @param8 IS NOT NULL THEN [Qwik Due] = 'CAST(@param8 AS nvarchar(20))'else '' End) and
(CASE WHEN @param9 IS NOT NULL THEN [Qwik sent date] = 'CAST(@param9 AS nvarchar(20))'else '' End) and
(CASE WHEN @param10 IS NOT NULL THEN [Final due] = 'CAST(@param10 AS nvarchar(20))'else '' End) and
(CASE WHEN @param11 IS NOT NULL THEN [Final sent date] = 'CAST(@param11 AS nvarchar(20))'else '' End) and
(CASE WHEN @param12 IS NOT NULL THEN [on hold code] = '@param12' else '' End)
End
I would really appreciate if you can do it as soon as possible.
June 3, 2003 at 9:50 pm
PC, you are missing out on a lot since dynamic sql used correctly can yield very good results. Although I did not use it in my example, properly parameterized dynamic sql using sp_executesql is sometimes much better than a proc that has tgo handle multiple scenarios. Plans are cached when using sp_executesql and the sql can be very specific. I have seen great performance gains is some instances using very specific dynamic sql rather than very open ended stored procs.
June 4, 2003 at 1:26 pm
David asked for an example of the UDF that was referenced in my previous post. But before you review the code, David said "It sounds to me like all you've done is move the dynamic aspect of the sql to your udf?" Well yes and no. Yes, the UDF dynamicaly identifies the rows that should be returned to the caller and No, there is absolutely no usage of dynamic SQL anywhere. (I guess you wouldn't know that cause I didn't include the UDF in my previous post.)
Most novice TSQL coders are unaware of the issues and risks associated with the use of Dynamic SQL i.e.; the user executing your code must have explicit permissions to do what the dynamic SQL is trying to do. I never grant permissions (SELECT or otherwise) to any user (and I rarely use views). Also, you open up the possibility for Injection attacks. I believe that Dynamic SQL should only be used by DBA's for utility functions and never ever used in any application code.
Anyway, here is an example of a Multi-Statement table-valued function that was referenced in my previous post. this forum does not deal with tabs & spaces well so the indenting is off (for complex IF/ELSE blocks you really need indenting!):
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_CustomerSearch]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_CustomerSearch]
GO
dbo.udf_CustomerSearch(@Lastname
,@City
,@PhoneNumber)
--// Used in FlightFare_Availability to retrieve a table of Flights based on standared criteria
CREATE FUNCTION dbo.udf_CustomerSearch (@Lastname varchar(50)
,@City varchar(30)
,@PhoneNumber varchar(20)
)
RETURNS @CustomerIDs
TABLE (CustomerID int NOT NULL PRIMARY KEY
)
AS
BEGIN
IF @LastName IS NOT NULL
IF @City IS NOT NULL
IF @PhoneNumber IS NOT NULL
INSERT @CustomerIDs
SELECT CustomerID
FROM Customer c
JOIN CustomerAddress ca
ON c.CustomerID = ca.CustomerID
JOIN CustomerPhone cp
ON c.CustomerID = cp.CustomerID
AND cp.PhoneType = 'Home'
WHERE c.LastName = @LastName
AND ca.City = @City
AND cp.PhoneNumber = @PhoneNumber
ELSE
INSERT @CustomerIDs
SELECT CustomerID
FROM Customer c
JOIN CustomerAddress ca
ON c.CustomerID = ca.CustomerID
JOIN CustomerPhone cp
ON c.CustomerID = cp.CustomerID
AND cp.PhoneType = 'Home'
WHERE c.LastName = @LastName
AND ca.City = @City
ELSE
INSERT @CustomerIDs
SELECT CustomerID
FROM Customer c
JOIN CustomerAddress ca
ON c.CustomerID = ca.CustomerID
JOIN CustomerPhone cp
ON c.CustomerID = cp.CustomerID
AND cp.PhoneType = 'Home'
WHERE c.LastName = @LastName
AND ca.City = @City
AND cp.PhoneNumber = @PhoneNumber
ELSE
IF @City IS NOT NULL
IF @PhoneNumber IS NOT NULL
ELSE
ELSE
IF @PhoneNumber IS NOT NULL
INSERT @CustomerIDs
SELECT CustomerID
FROM Customer c
JOIN CustomerAddress ca
ON c.CustomerID = ca.CustomerID
JOIN CustomerPhone cp
ON c.CustomerID = cp.CustomerID
AND cp.PhoneType = 'Home'
WHERE c.LastName = @LastName
AND ca.City = @City
AND cp.PhoneNumber = @PhoneNumber
ELSE
raiserror
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
June 4, 2003 at 1:36 pm
quote:
PC, you are missing out on a lot since dynamic sql used correctly can yield very good results. Although I did not use it in my example, properly parameterized dynamic sql using sp_executesql is sometimes much better than a proc that has tgo handle multiple scenarios. Plans are cached when using sp_executesql and the sql can be very specific. I have seen great performance gains is some instances using very specific dynamic sql rather than very open ended stored procs.
Yes I know, but you are still requiring that the user of your sp have permissions on the base tables that you reference in your query. In my application, execute permissions are granted to the called stored procedures only. I do not grant any permissions on subroutines stored procedures, UDF's, or tables. If a user were to gain access to my SQL server outside of the application they can do nothing except execute the procedures they could execute easily from the application.
I have not had a problem with performance or recompilation of sp's using the UDF approach and my servers are safe and secure from unauthorized use and Injection hacking.
June 9, 2003 at 9:02 am
Hi - the easiest way is just to set each parameter default = '%' and then say
select * from case30
where col1 like @param1 etc. Could be inefficient if they don't supply many parameters. Otherwise you could put your SQL in a variable and add to the statement if the parameter has a value e.g.
declare @stat varchar(8000)
select @stat = 'select * from case30 '
if @param1 is not null select @stat = @stat + 'where col1 = ' + @param
Getting the number of quotes correct if the parameters are character can be tricky
e.g. + 'where col1 = ''' + @param + ''''
Hi guys,
I would really appreciate if you can help me out. My problem is that I have 2 tables named Case30 and Caseold. Each of the table has 10 fields. Case 30 contains cases that is 30 days old while caseold table contains records till 2 years. According to the entry of the user in Visual Basic form in any of the 10 fields I have to fetch the recordset from both of the tables. I want it to be done as stored procedure using dynamic queries. Efficiency of the query is also important. The thing is that how would i query those tables on the basis of any of those 10 inputs provided by the user. The user might enter just 1 of the field and leave other fields blank or enter all of them to query the resultset. How would you able to query against depending on the no. of inputs provided by user?
Hoping to have your reply as soon as possible.
[/quote]
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply