T-SQL Concatenation Question... 4

[pzmgmb]

IF @Date is NULL
         BEGIN
            SELECT Task 
            From Tasks
            Where LocationID = @LocationID
         END
      ELSE
         BEGIN
            SELECT Task 
            From Tasks
            Where LocationID = @LocationID and Date = @Date
         END

As you can see the SELECT and the FROM parts never change, the WHERE clause is the only thing that changes.

How would I concatentate the SELECT and FROM into a string variable and then i'll have an IF statement decide which WHERE i need and concatenate that onto the string variable.

Then how will i execute the SQL contained in the string to return the result set?

 

[SQLSister]

What you are asking to do would be a bad practice as it means you must use dynamic SQL which is to be avoided. Have you tried?

SELECT Task 
From Tasks
Where LocationID = @LocationID and 
(@Date is Null or Date = @Date)

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[dbomrrsm]

may have to play around with the format of the cast(@date) but this is a start.

Declare @SQL varchar (4000),@Date datetime,@locationid int
set @date = getdate()
set @locationid = 15
IF @Date is NULL
         BEGIN
            set @SQL = 'SELECT Task 
            From Tasks
            Where LocationID = '+cast(@LocationID as varchar)
         END
      ELSE
         BEGIN
	Set @SQL = 
            'SELECT Task 
            From Tasks
            Where LocationID = '+cast(@LocationID as varchar)+
		' and Date = '+cast(@Date as varchar)
         END

PRINT @SQL
EXEC (@SQL)

[blue]DBomrrsm[/blue]

 

[pzmgmb]

Thank you both for your posts. SQLSister may I ask why dynamic SQL is to be avoided?

 

[SQLSister]

Because it is less secure and less efficient. It is also somehwat harder to manage the permissions for since you must set them at the table level rather than just setting the Exec permission on the SP.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[pzmgmb]

Thank you SQLSister! I implemented your suggestion yesterday and I love it! I gave you a star for your idea and I'm going to give you another one because I like your idea. What I love about it is that i have two optionals in my actual WHERE and i can still open it in an Access ADP and get a graphical representation of it!

Kudos.

 

[nikhilparchure]

Hi pz,
effectively
((@Date is Null or Date = @Date)) can be more streamlined
to make the query look like

SELECT Task
From Tasks
Where LocationID = @LocationID and
Date = Isnull(@Date,Date) ...

If the @date is null then it makes a self join query which is what you require

 

[pradipto]

Hi Guys,

Thanks for the very interesting post. (I am a new bee and) I have some problems that were very similar to above. I was wondering you guys can give some suggestion on this query, i.e. whether it can be optimized further similar to what has been suggested above (my importance is on the speed of the query etc):

(p.s. as you can see in the code below, I have just modified it to the suggestion that Nikhil gave above)

In the code below, the only difference between @Tmp_CodeInd=1 and @Tmp_CodeInd=2 is that the tables used are different i.e. PrcDly versus IdxDaily

If @Tmp_CodeInd=1
Insert Into @Tmp_Output (Date_)
Select PrcDly.Date_
From PrcDly
Where PrcDly.Code=@Tmp_Code
and PrcDly.Date_
between IsNull(@Start_Date,PrcDly.Date_)
and IsNull(@End_Date,PrcDly.Date_)

Else
If @Tmp_CodeInd=2
Insert Into @Tmp_Output (Date_)
Select IdxDaily.Date_
From IdxDaily
Where IdxDaily.Code=@Tmp_Code
and IdxDaily.Date_ between IsNull(@Start_Date,IdxDaily.Date_)
and IsNull(@End_Date,IdxDaily.Date_)


Would be very grateful if any one can give me some suggestions ...

Thanks.

 

[pradipto]

Hi Nikhil,

I think the self join idea is not a speed efficient one, I was tring out with and without the self join on some very large queries. The "without the self join" idea of SQL Sister works anywhere from 8-20 times faster (depending on the size of the query) than the "with the self join" idea.

Thought to let u know.

Regards.