Dynamic where Clause
-
1. With the statement below I was able to retrieved records using the @jv parameter. declare @jv varchar(100) set @jv = 'abh3' SELECT jv,day, interval/4, sm, aiu, sum(att), ans,usg, acblk FROM c204 WHERE [day] between '7-21-2003' and '7-22-2003' and jv = COALESCE(@jv,jv) GROUP BY jv,day,interval/4,sm,aiu,ans,usg,acblk ORDER BY jv,day,interval/4,sm,aiu,ans,usg,acblk GO 2. What is the solution if you have two value in one parameter like the example below, Does anyone know the answer. Please HELP :confused: I'm using it in my stored procdure. declare @jv varchar(100) set @jv = 'abh3','hal3' SELECT jv,day, interval/4, sm, aiu, sum(att), ans,usg, acblk FROM c204 WHERE [day] between '7-21-2003' and '7-22-2003' and jv = COALESCE(@jv,jv) GROUP BY jv,day,interval/4,sm,aiu,ans,usg,acblk ORDER BY jv,day,interval/4,sm,aiu,ans,usg,acblk GO Dabuskol
-
1. With the statement below I was able to retrieved records using the @jv parameter. declare @jv varchar(100) set @jv = 'abh3' SELECT jv,day, interval/4, sm, aiu, sum(att), ans,usg, acblk FROM c204 WHERE [day] between '7-21-2003' and '7-22-2003' and jv = COALESCE(@jv,jv) GROUP BY jv,day,interval/4,sm,aiu,ans,usg,acblk ORDER BY jv,day,interval/4,sm,aiu,ans,usg,acblk GO 2. What is the solution if you have two value in one parameter like the example below, Does anyone know the answer. Please HELP :confused: I'm using it in my stored procdure. declare @jv varchar(100) set @jv = 'abh3','hal3' SELECT jv,day, interval/4, sm, aiu, sum(att), ans,usg, acblk FROM c204 WHERE [day] between '7-21-2003' and '7-22-2003' and jv = COALESCE(@jv,jv) GROUP BY jv,day,interval/4,sm,aiu,ans,usg,acblk ORDER BY jv,day,interval/4,sm,aiu,ans,usg,acblk GO Dabuskol
dabuskol wrote: set @jv = 'abh3','hal3' Not quite sure what you are doing here. SQL Server does not support arrays so it does not seem possible you will have two values in one parameter. You can break them up manually and then build the query and execute it dynamically. Rocky Moore <><
-
1. With the statement below I was able to retrieved records using the @jv parameter. declare @jv varchar(100) set @jv = 'abh3' SELECT jv,day, interval/4, sm, aiu, sum(att), ans,usg, acblk FROM c204 WHERE [day] between '7-21-2003' and '7-22-2003' and jv = COALESCE(@jv,jv) GROUP BY jv,day,interval/4,sm,aiu,ans,usg,acblk ORDER BY jv,day,interval/4,sm,aiu,ans,usg,acblk GO 2. What is the solution if you have two value in one parameter like the example below, Does anyone know the answer. Please HELP :confused: I'm using it in my stored procdure. declare @jv varchar(100) set @jv = 'abh3','hal3' SELECT jv,day, interval/4, sm, aiu, sum(att), ans,usg, acblk FROM c204 WHERE [day] between '7-21-2003' and '7-22-2003' and jv = COALESCE(@jv,jv) GROUP BY jv,day,interval/4,sm,aiu,ans,usg,acblk ORDER BY jv,day,interval/4,sm,aiu,ans,usg,acblk GO Dabuskol
Hi There Looks like you will have to revert to Dynamic sql. Build up the statement as required and then excecute. This is normally a bit slower than straight sql but should be able to do the trick. Regards Peet Schultz USE [PUBS] DECLARE @LAST_NAME NVARCHAR(100) DECLARE @SQL NVARCHAR(1000) SET @LAST_NAME = '(''White'',''Green'')' SET @SQL = 'SELECT * FROM AUTHORS WHERE [AU_LNAME] IN' + @LAST_NAME EXEC (@SQL)
-
Hi There Looks like you will have to revert to Dynamic sql. Build up the statement as required and then excecute. This is normally a bit slower than straight sql but should be able to do the trick. Regards Peet Schultz USE [PUBS] DECLARE @LAST_NAME NVARCHAR(100) DECLARE @SQL NVARCHAR(1000) SET @LAST_NAME = '(''White'',''Green'')' SET @SQL = 'SELECT * FROM AUTHORS WHERE [AU_LNAME] IN' + @LAST_NAME EXEC (@SQL)