SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sunil Chandurkar / Writing Dynamic Stored Procedure / Latest Posts

RE: Writing Dynamic Stored Procedure

Muhammad Azeem-433893 — Wed, 10 Jun 2009 04:06:39 GMT

isNull funcation for nullable value ... try then reply

RE: Writing Dynamic Stored Procedure

Dave Ballantyne — Thu, 04 Jun 2009 03:56:49 GMT

Interesting.....I can now get multiple index seeks :-D[code]drop proc test2 gocreate proc test2 @ProductId integer, @ReferenceOrderID integer, @Quantity integerasselect *from [Production].[TransactionHistory]where (@productid is not null and @productid = ProductID)or (@ReferenceOrderID is not null and @ReferenceOrderID = ReferenceOrderID) --or (@quantity is not null and @quantity = Quantity) Uncomment This to force a scangoexec test2 @productid = 790,@ReferenceOrderID = NULL,@Quantity=NULL goexec test2 @productid = NULL,@ReferenceOrderID =61197 ,@Quantity=NULL[/code]It seems to seek if ALL the referenced columns are indexed, but uncommenting the Quantity lookup a scan takes place.Edit : Ive updated my blog [url]http://sqlandthelike.blogspot.com/[/url] with this info

RE: Writing Dynamic Stored Procedure

kevin mann — Wed, 03 Jun 2009 12:36:28 GMT

this does not help if you have nullable fields

RE: Writing Dynamic Stored Procedure

Muhammad Azeem-433893 — Wed, 03 Jun 2009 10:15:35 GMT

sunil,You can use this style too....set @Gender = isNull('M', '_')set @Age = isNull(22, 0)SELECT * FROM test_EmployeeDetailsWhere Gender LIKE @Gender AND Age > @AgeThanks!Azeem

RE: Writing Dynamic Stored Procedure

Jeffrey Williams 3188 — Wed, 03 Jun 2009 09:20:37 GMT

[quote][b]Dave Ballantyne (6/3/2009)[/b][hr]Jeffrey ,Can you post a full XML query plan of one of your queries that Seek ?.If you could try to make the query as simple as possible though, just one column / variable.We may be able to gleam a vital piece of information from that.[/quote]I'll put together a few execution plans and post the results. The query is fairly simple already and does not return a lot of columns. The join is a bit complex with multiple columns and a date range - but the execution plans are still fairly simple.

RE: Writing Dynamic Stored Procedure

changbluesky — Wed, 03 Jun 2009 05:34:09 GMT

good artical..but it's good useful for the dynamic sql when using 'sp_executesql'

RE: Writing Dynamic Stored Procedure

GilaMonster — Wed, 03 Jun 2009 02:29:05 GMT

[quote][b]TheSQLGuru (6/2/2009)[/b][hr][code]alter PROCEDURE Test4 ( @ProdID int = null, @Qty int = null)ASselect TransactionIDfrom [Production].[TransactionHistory] -- with (index = [IX_TransactionHistory_ProductID])where ProductID BETWEEN coalesce(@ProdID, 0) AND coalesce(@ProdID, 99999999) --should use actual limits of INT here! AND Quantity BETWEEN coalesce(@Qty, 0) AND coalesce(@Qty, 99999999)GO[/code]Exec Test4 @ProdID = 7904.16 cost with forced index, 11 IO0.711 cost without forced index (CI scan), 792 IOdue to the mathematics of the optimizer (i.e. the MUCH higher cost associated with the known-to-be-not-sequential-io index seek/bookmark lookup the query plan cost of seeking/lookup 2 rows is MUCH higher than scaning the entire table despite significantly fewer total IOs.Gail, I wonder if your larger table would still be more efficient doing the scan than with the forced seek?[/quote]It is. The cost of the forced index is way higher and the IOs are slightly higher.CI ScanTable 'TransactionHistory'. Scan count 3, logical reads 7367, physical reads 0Cost 7.19Forced indexTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0Table 'TransactionHistory'. Scan count 3, logical reads 8109, physical reads 0 -- 2641 key lookups.Cost 35.02

RE: Writing Dynamic Stored Procedure

GilaMonster — Wed, 03 Jun 2009 02:23:07 GMT

[quote][b]Dave Ballantyne (6/3/2009)[/b][hr]Jeffrey ,Can you post a full XML query plan of one of your queries that Seek ?.If you could try to make the query as simple as possible though, just one column / variable.We may be able to gleam a vital piece of information from that.[/quote]I'd also like to see the plans, but with at least 2 columns/variables in the where and, if possible, a couple of different plans based on different parameters passed.

RE: Writing Dynamic Stored Procedure

Dave Ballantyne — Wed, 03 Jun 2009 02:04:34 GMT

Jeffrey ,Can you post a full XML query plan of one of your queries that Seek ?.If you could try to make the query as simple as possible though, just one column / variable.We may be able to gleam a vital piece of information from that.

RE: Writing Dynamic Stored Procedure

GilaMonster — Wed, 03 Jun 2009 00:38:19 GMT

[quote][b]Jeffrey Williams (6/2/2009)[/b][hr]Nope - SQL Server 2005 SP3 and had the same experience on 2005 SP2 CU7.[/quote]Odd. I've played around with RECOMPILE on these before and on 2005 I always get 'average' plans. Might be the table size. I don't test on anything under 500 000 rows

RE: Writing Dynamic Stored Procedure

jovannybrea — Tue, 02 Jun 2009 18:11:38 GMT

What if the age is not null, is empty ? I think this is kind of problem..

RE: Writing Dynamic Stored Procedure

Jeffrey Williams 3188 — Tue, 02 Jun 2009 17:35:25 GMT

Nope - SQL Server 2005 SP3 and had the same experience on 2005 SP2 CU7.

RE: Writing Dynamic Stored Procedure

GilaMonster — Tue, 02 Jun 2009 16:43:50 GMT

RE: Writing Dynamic Stored Procedure

Jeffrey Williams 3188 — Tue, 02 Jun 2009 15:54:38 GMT

[quote][b]TheSQLGuru (6/2/2009)[/b]Perhaps because they found that their poorly written code (IS NULL OR constructs) required it to perform acceptably?!? :hehe:Still not sure about that being the issue though. Can you restore a backup of your database (probably pretty darn big given the table sizes you listed) and then set forced parameterization off and see if you get the same query plans?? Or maybe just change production for a few minutes off hours (if doable obviously)?[/quote]More likely just the way they call the procedures - or, it could be the fact that they have a lot of cursors in their code :)As for restoring a copy - not going to be possible at this time. I don't have the storage to restore another copy of a 350GB database, which is also copied for the mirror and also a copy available for the test system. At least, not at the moment.

RE: Writing Dynamic Stored Procedure

TheSQLGuru — Tue, 02 Jun 2009 15:09:31 GMT

[quote]The vendor that supplies the application that uses this system recommended setting this parameter. In their testing they found a significant improvement for their application.[/quote]Perhaps because they found that their poorly written code (IS NULL OR constructs) required it to perform acceptably?!? :hehe:Still not sure about that being the issue though. Can you restore a backup of your database (probably pretty darn big given the table sizes you listed) and then set forced parameterization off and see if you get the same query plans?? Or maybe just change production for a few minutes off hours (if doable obviously)?

RE: Writing Dynamic Stored Procedure

Jeffrey Williams 3188 — Tue, 02 Jun 2009 14:16:26 GMT

[quote][b]TheSQLGuru (6/2/2009)[/b][hr]1) I am curious what among these listed reasons where why you chose to do forced parameterization for your database. See here in BOL:ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/068282b7-c419-4e2c-990a-1f8b7c12762f.htmI do have a client with a metric butt-ton of really horrible looping ADOc code and we picked up 30-35% throughput gains with forced parameterization. 2) doesn't make a difference on my copy (sql 2005 sp2):use mastergoalter database AdventureWorks set PARAMETERIZATION FORCEDgouse adventureworksgodbcc freeproccachegoexec test2 @productid = 790use mastergoalter database AdventureWorks set PARAMETERIZATION SIMPLEgo[/quote]The vendor that supplies the application that uses this system recommended setting this parameter. In their testing they found a significant improvement for their application.

RE: Writing Dynamic Stored Procedure

TheSQLGuru — Tue, 02 Jun 2009 14:03:45 GMT

1) I am curious what among these listed reasons where why you chose to do forced parameterization for your database. See here in BOL:ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/068282b7-c419-4e2c-990a-1f8b7c12762f.htmI do have a client with a metric butt-ton of really horrible looping ADOc code and we picked up 30-35% throughput gains with forced parameterization. 2) doesn't make a difference on my copy (sql 2005 sp2):use mastergoalter database AdventureWorks set PARAMETERIZATION FORCEDgouse adventureworksgodbcc freeproccachegoexec test2 @productid = 790use mastergoalter database AdventureWorks set PARAMETERIZATION SIMPLEgo

RE: Writing Dynamic Stored Procedure

Jeffrey Williams 3188 — Tue, 02 Jun 2009 13:21:59 GMT

[quote][b]TheSQLGuru (6/2/2009)[/b]Maybe with that many rows the optimizer is simply hoping it "gets lucky" and the seek/lookup plan is less costly that the massive cost of a table scan! :w00t:[/quote]Maybe :)I am still confused by this - because I am getting optimal plans for each case with optional criteria and I should not. At least, according to everything I have read on this thread and others - using this format I should not be getting an optimal plan.I have also tested this by removing the check for an is null parameter - the plan is the same, uses the same indexes and performs the same for the different possibilities.I am really wondering if setting the parameterization to forced is why this works. I really need to setup a test database and check this out.

RE: Writing Dynamic Stored Procedure

TheSQLGuru — Tue, 02 Jun 2009 11:55:15 GMT

[quote][b]Jeffrey Williams (6/2/2009)[/b][hr]Gail, I really don't want to argue this point - but I am having a small problem here. I have a query where I use this construct and include the with recompile option, and the actual execution plans for different criteria all use index seeks where appropriate.I have tested this multiple times and still see index seeks being selected for each plan with different parameters.Now, I think I understand what is happening and why it works on my system - but I really need to setup a test. I just have not had time yet.Can you see what happens if you modify the parameterization option from simple to forced? Does this make any difference at all? If not, then I really don't understand why I can get index seeks on this procedure and very good performance.BTW - the tables I am accessing are:PatientAccessLog 51,867,860 rowsAuditLog 198,028,731 rows[/quote]Maybe with that many rows the optimizer is simply hoping it "gets lucky" and the seek/lookup plan is less costly that the massive cost of a table scan! :w00t:

RE: Writing Dynamic Stored Procedure

Dave Ballantyne — Tue, 02 Jun 2009 11:38:41 GMT

[quote]Wouldn't the scan be expected with the additional criteria since Quantity is not part of nor included in the index?If I make the index ProductID, Quantity or add Quantity as an included column I still get a seek.[/quote]Im guessing that the optimizer is ignoring the Quantity SARG's as @Quantity is NULL.Cant prove that an the moment though

RE: Writing Dynamic Stored Procedure

Jeffrey Williams 3188 — Tue, 02 Jun 2009 10:58:52 GMT

Gail, I really don't want to argue this point - but I am having a small problem here. I have a query where I use this construct and include the with recompile option, and the actual execution plans for different criteria all use index seeks where appropriate.I have tested this multiple times and still see index seeks being selected for each plan with different parameters.Now, I think I understand what is happening and why it works on my system - but I really need to setup a test. I just have not had time yet.Can you see what happens if you modify the parameterization option from simple to forced? Does this make any difference at all? If not, then I really don't understand why I can get index seeks on this procedure and very good performance.BTW - the tables I am accessing are:PatientAccessLog 51,867,860 rowsAuditLog 198,028,731 rows

RE: Writing Dynamic Stored Procedure

TheSQLGuru — Tue, 02 Jun 2009 10:54:24 GMT

[code]alter PROCEDURE Test4 ( @ProdID int = null, @Qty int = null)ASselect TransactionIDfrom [Production].[TransactionHistory] -- with (index = [IX_TransactionHistory_ProductID])where ProductID BETWEEN coalesce(@ProdID, 0) AND coalesce(@ProdID, 99999999) --should use actual limits of INT here! AND Quantity BETWEEN coalesce(@Qty, 0) AND coalesce(@Qty, 99999999)GO[/code]Exec Test4 @ProdID = 7904.16 cost with forced index, 11 IO0.711 cost without forced index (CI scan), 792 IOdue to the mathematics of the optimizer (i.e. the MUCH higher cost associated with the known-to-be-not-sequential-io index seek/bookmark lookup the query plan cost of seeking/lookup 2 rows is MUCH higher than scaning the entire table despite significantly fewer total IOs.Gail, I wonder if your larger table would still be more efficient doing the scan than with the forced seek?

RE: Writing Dynamic Stored Procedure

Jack Corbett — Tue, 02 Jun 2009 10:02:33 GMT

[quote][b]GilaMonster (6/2/2009)[/b][hr][quote][b]TheSQLGuru (6/2/2009)[/b][hr]Interesting. You also get a seek if you simply hard code values for coalesce (and I presume isnull) such as this example. It avoids having to hit the table for the pair of MAXs:[/quote]Yup, though as soon as you add a second condition, it goes back to a clustered index scan (at least for me). What do you get here? Index scan/Bookmark Lookup?[code]ALTER PROCEDURE Test2 ( @ProdID int = null, @Qty int = null)ASselect TransactionIDfrom [Production].[TransactionHistory]where ProductID BETWEEN coalesce(@ProdID, 0) AND coalesce(@ProdID, 99999999) --should use actual limits of INT here! AND Quantity BETWEEN coalesce(@Qty, 0) AND coalesce(@Qty, 99999999)GOExec Test2 @prodID = 790[/code][/quote]Wouldn't the scan be expected with the additional criteria since Quantity is not part of nor included in the index?If I make the index ProductID, Quantity or add Quantity as an included column I still get a seek.

RE: Writing Dynamic Stored Procedure

Jack Corbett — Tue, 02 Jun 2009 09:55:57 GMT

[quote][b]TheSQLGuru (6/2/2009)[/b][hr]Interesting. You also get a seek if you simply hard code values for coalesce (and I presume isnull) such as this example. It avoids having to hit the table for the pair of MAXs:select TransactionID from [Production].[TransactionHistory]where ProductID BETWEEN coalesce(@ProductID, 0) AND coalesce(@ProductID, 99999999) --should use actual limits of INT here!You don't get the seek/bookmark lookup if you do select * with that query. Still a CI scan.[/quote]I've used this in the past after having read an article about it somewhere, I don't think it was on SSC. Of course the Select * will give a scan because you'd have to do a seek and a lookup with the Select * so the scan is probably faster.

RE: Writing Dynamic Stored Procedure

GilaMonster — Tue, 02 Jun 2009 09:49:49 GMT

[quote][b]TheSQLGuru (6/2/2009)[/b][hr]Interesting. You also get a seek if you simply hard code values for coalesce (and I presume isnull) such as this example. It avoids having to hit the table for the pair of MAXs:[/quote]Yup, though as soon as you add a second condition, it goes back to a clustered index scan (at least for me). What do you get here? Index scan/Bookmark Lookup?[code]ALTER PROCEDURE Test2 ( @ProdID int = null, @Qty int = null)ASselect TransactionIDfrom [Production].[TransactionHistory]where ProductID BETWEEN coalesce(@ProdID, 0) AND coalesce(@ProdID, 99999999) --should use actual limits of INT here! AND Quantity BETWEEN coalesce(@Qty, 0) AND coalesce(@Qty, 99999999)GOExec Test2 @prodID = 790[/code]

RE: Writing Dynamic Stored Procedure

TheSQLGuru — Tue, 02 Jun 2009 09:38:39 GMT

Interesting. You also get a seek if you simply hard code values for coalesce (and I presume isnull) such as this example. It avoids having to hit the table for the pair of MAXs:select TransactionID from [Production].[TransactionHistory]where ProductID BETWEEN coalesce(@ProductID, 0) AND coalesce(@ProductID, 99999999) --should use actual limits of INT here!You don't get the seek/bookmark lookup if you do select * with that query. Still a CI scan.

RE: Writing Dynamic Stored Procedure

GilaMonster — Tue, 02 Jun 2009 09:28:22 GMT

[quote][b]self.soul.friend (6/2/2009)[/b][hr]Is this really inefficient on large tables?[/quote]Yes.It does not (and cannot) use indexes properly. Table scans are not much fun on million+ row tables.

RE: Writing Dynamic Stored Procedure

self.soul.friend — Tue, 02 Jun 2009 09:17:37 GMT

I suppose I may be confused, but I prefer to write this type of query with isnull... [code]where isnull(@Gender,Gender) = Gender and isnull(@age+1,age)<=age[/code]gives the same results as the nested OR statement (I'm not a fan of the @age+1, but the original query returns an empty set when asking for age=30 even though there is a record with an age of 30).Is this really inefficient on large tables?

RE: Writing Dynamic Stored Procedure

Dave Ballantyne — Tue, 02 Jun 2009 07:50:41 GMT

Ive Accumlated all the methods i can think of into one proc,[code]drop proc test2gocreate proc test2 (@productid int = null, @quantity int = null)asset nocount onselect TransactionIdfrom [Production].[TransactionHistory]where (@productid is null or @productid = ProductID) select TransactionIdfrom [Production].[TransactionHistory]where (@productid is null or productid = @ProductID)select TransactionIdfrom [Production].[TransactionHistory]where productid = coalesce(@ProductID,ProductId)select TransactionIdfrom [Production].[TransactionHistory]where productid = CASE WHEN @ProductId IS NULL THEN ProductId ELSE @ProductId ENDdeclare @MinInt integerDeclare @MaxInt integerSelect @MinInt = min(productid) , @MaxInt = max(productid) from [Production].[TransactionHistory] select *from [Production].[TransactionHistory]where productid BETWEEN coalesce(@productid, @MinInt) AND coalesce(@productid, @MaxInt) select TransactionId from [Production].[TransactionHistory]where productid BETWEEN coalesce(@productid, @MinInt) AND coalesce(@productid, @MaxInt) select *from [Production].[TransactionHistory]where productid BETWEEN coalesce(@productid, @MinInt) AND coalesce(@productid, @MaxInt) select TransactionId from [Production].[TransactionHistory]where productid BETWEEN isnull(@productid, @MinInt) AND isnull(@productid, @MaxInt) select * from [Production].[TransactionHistory]where productid BETWEEN isnull(@productid, @MinInt) AND isnull(@productid, @MaxInt)go dbcc freeproccachego exec test2 @productid = 790[/code]The results from the last 4 are quite interesting ;-)

RE: Writing Dynamic Stored Procedure

TheSQLGuru — Tue, 02 Jun 2009 07:06:54 GMT

[quote][b]Dave Ballantyne (6/2/2009)[/b][hr]I dont think you attached the right plan, that one has an index scan[/quote]DOH!!! Good catch Dave. My brain sure did see seek last night when I did this. My apologies all!! :(I tried this and it didn't get a seek either. Clearly I may be misremembering getting a seek plan on some variation of this in the past.use adventureworksgodrop proc test2gocreate proc test2 (@productid int = null, @quantity int = null)asset nocount on declare @prodid int, @quant intselect @prodid = @productid, @quant = @quantityselect *from [Production].[TransactionHistory]where (@prodid is null or @prodid = ProductID) --indexedand (@quant is null or @quant = Quantity) --not indexedgo

RE: Writing Dynamic Stored Procedure

Rob Reid-246754 — Tue, 02 Jun 2009 06:59:08 GMT

rafidheenmnotice in my post I said [b]"I used to"[/b] as in[b]"I used to use[/b] lots of CASE statements and IF branches to handle queries that needed to be dynamic but build on the server side using multiple proc params. For example something like this:"SQL mentioned and ammended by yourself[b]before changing [/b]to the dynamic version because of all the issues mentioned by Gila, SQLGuru etc

RE: Writing Dynamic Stored Procedure

Dave Ballantyne — Tue, 02 Jun 2009 06:56:56 GMT

I dont think you attached the right plan, that one has an index scan

RE: Writing Dynamic Stored Procedure

GilaMonster — Tue, 02 Jun 2009 06:56:33 GMT

I have a lot, lot more rows - 962662. Not due to MS. I padded out my copy of AW last year for my PASS presentation. Query time differences weren't that visible on smaller row sets and I wanted time differences that were noticable.I would imagine it's row count. The lookups will be just too expensive on the larger row set, even in the first execution. I'll see if I can find the original download of AW and recreate the DB.Index on ProductID is there.p.s. No seek in that plan. There's an index scan. Those I've often seen in these kind of queries. I'm wondering if it's possible to have an index seek, seeing that the seek predicate will not remain the same between executions with different parameters.

RE: Writing Dynamic Stored Procedure

TheSQLGuru — Tue, 02 Jun 2009 06:46:51 GMT

Gail, I ran that test set on SQL 2005 SP2 (can't install SP3 due to missing install code for SP2 apparently). I noticed that I have this index on the referenced table. It is certainly possible I added this in manually to the base database for some demonstration purpose and never deleted it! :)USE [AdventureWorks]GO/****** Object: Index [IX_TransactionHistory_ProductID] Script Date: 06/02/2009 07:41:13 ******/CREATE NONCLUSTERED INDEX [IX_TransactionHistory_ProductID] ON [Production].[TransactionHistory] ( [ProductID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOWith a clean proc cache I get that seek plan every time IF it is the first execution of the sproc.I attached the plan for your review. I suppose it would be a bummer if this is a query regression from 2005 to 2008. Oh, just in case they changed the number of rows, I have 113443 total rows in my table.

RE: Writing Dynamic Stored Procedure

djblyth — Tue, 02 Jun 2009 06:30:07 GMT

Looks like performance could suffer depending on the indexing available and the parameters available. Therefore, I would not use the technique unless the parameters simply to 'refine' a result set obtained after initial indexed selection logic in the WHERE clause.

RE: Writing Dynamic Stored Procedure

Dave Ballantyne — Tue, 02 Jun 2009 04:18:19 GMT

FWIW , i also get an index scan , i guess this conversation just goes to prove the sensitivity of this sort of query. Microsoft SQL Server 2005 - 9.00.4207.00 (Intel X86) Dec 17 2008 14:34:38 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

RE: Writing Dynamic Stored Procedure

GilaMonster — Tue, 02 Jun 2009 04:01:50 GMT

[quote][b]TheSQLGuru (6/1/2009)[/b][hr][code]use adventureworksgodrop proc testgocreate proc test (@productid int = null, @quantity int = null)asset nocount on select *from [Production].[TransactionHistory]where (@productid is null or @productid = ProductID) --indexedand (@quantity is null or @quantity = Quantity) --not indexedgodbcc freeproccachego--Gail, here is proof of an index seek for this query styleexec test @productid = 790--index seek, bookmark lookup (163 IO), 2 rows--not pretty hereexec test @productid = 784--index seek, bookmark lookup (2395 IO), 746 rows--UGLYexec test ----index seek, bookmark lookup (340486 IO!!!)[/code][/quote]What version of SQL are you running that on? I get clustered index scans for all of those (parallel scans, to be precise). (see attached)If I include the maxdop of 1 then I still get clustered index scans. Might be because I loaded more data into AW for a presentation last yearSQL 2008 RTM.Can you attach your exec plan please? I'd like to take a look at it, see if I can figure why I'm not getting the same plan.

RE: Writing Dynamic Stored Procedure

GilaMonster — Tue, 02 Jun 2009 03:39:53 GMT

[quote][b]rafidheenm (6/2/2009)[/b][hr]This can be write like this and avoid CASE statements from WHERE clause..[/quote]Yes, but it doesn't change the execution characteristics. Just like the case statement version, that will typically run with a clustered index/table scan.

RE: Writing Dynamic Stored Procedure

rafidheenm — Tue, 02 Jun 2009 03:19:38 GMT

This can be write like this and avoid CASE statements from WHERE clause..[code]CREATE PROC test@Stamp datetime,@FirstName varchar(25) = NULL,@Surname varchar(25) = NULL,@Age int = NULL,@Address1 varchar(40) = NULLASBEGINDECLARE @Today BITSELECT @Today = CASE Datediff(day,@Stamp,getdate()) WHEN 0 THEN 1 ELSE 0 ENDIF @Today = 1BEGINSELECT FirstName, SureName, Age, Address1FROM DAILYWHERE FirstName = isnull(@FirstName, FirstName) AND Surname = isnull(@Surname ,Surname )AND Age = isnull(@Age, Age )AND Address1 = isnull(@Address1,Address1)ENDELSEBEGINSELECT FirstName, SureName, Age, Address1FROM HISTORICALWHERE FirstName = isnull(@FirstName,FirstName)AND Surname = isnull(@Surname,Surname)AND Age = isnull(@Age,Age)AND Address1 = isnull(@Address1 ,Address1)[/code]

RE: Writing Dynamic Stored Procedure

abc-454744 — Mon, 01 Jun 2009 22:55:47 GMT

The dificulty is also there when we want to use "like" or "in" oprator in the stored procedure, for these conditions we have to use dynamic querries.