SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


forming a dynamic query


forming a dynamic query

Author
Message
ssurekha2000
ssurekha2000
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 207
have a sp with 4 parameters the values of this parameters is obtained from application

currently i have

@transfrmdt date,
@transtodt date,
@cid integer,
@Type char(1)

DEClare @strqry varchar(max)
DEClare @str varchar(max)

If (@Type<>'')
BEGIN
SET @strqry= @strqry +' and type='''+@Type+''''

END

If (@cname<>'')
BEGIN
SET @strqry= @strqry +'select name from Mas_C where cid='''+@cid+''''
END




SET @str =' SELECT convert(varchar (10),transdt,111)as Transdt,amt
FROM Transcation WHERE 1=1
and Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111) '
+@strqry

print @str
exec(@str)
SELECT sum(Amt) as TotalAmt
FROM Transcation WHERE Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111)

i am not getting the result
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7468 Visits: 4830
ssurekha2000 (5/1/2014)
have a sp with 4 parameters the values of this parameters is obtained from application

currently i have

@transfrmdt date,
@transtodt date,
@cid integer,
@Type char(1)

DEClare @strqry varchar(max)
DEClare @str varchar(max)

If (@Type<>'')
BEGIN
SET @strqry= @strqry +' and type='''+@Type+''''

END

If (@cname<>'')
BEGIN
SET @strqry= @strqry +'select name from Mas_C where cid='''+@cid+''''
END




SET @str =' SELECT convert(varchar (10),transdt,111)as Transdt,amt
FROM Transcation WHERE 1=1
and Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111) '
+@strqry

print @str
exec(@str)
SELECT sum(Amt) as TotalAmt
FROM Transcation WHERE Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111)

i am not getting the result


I take it that print @str does not give you the query you are looking for. I suspect that you need to look at the order in which you are building up the string.
Initially you SET @strqry= @strqry +' and type='''+@Type+'''' then you set it to 'select name from Mas_C where cid='''+@cid+''''

From what I can see you need to swap the order of these, also it might help to set a value for @strqry then add any "where " filters later.

-------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx
ssurekha2000
ssurekha2000
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 207
swap what?
i am not getting any results
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7468 Visits: 4830
What is the result of print @str ?
I think it will be empty as you are building @strqry up with a potential outcome that it will be null. When you add the to @str you will end up with a null string and therefore no query to run.

The following is one way round the problem

@transfrmdt date,
@transtodt date,
@cid integer,
@Type char(1)

DEClare @strqry varchar(max)
DEClare @str varchar(max)

set @strqry= 'select name from Mas_C where 1 = 1 '

If (@Type<>'')
BEGIN
SET @strqry= @strqry +' and type='''+@Type+''''

END

If (@cname<>'')
BEGIN
SET @strqry= @strqry +' and cid='''+@cid+''''
END




SET @str =' SELECT convert(varchar (10),transdt,111)as Transdt,amt
FROM Transcation WHERE 1=1
and Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111) '
+@strqry

print @str
exec(@str)
SELECT sum(Amt) as TotalAmt
FROM Transcation WHERE Transdt between convert(varchar (10),@transfrmdt,111) and convert(varchar (10),@transtodt,111)



(Edited to add)

I ran the query above (with @type and @cname as empty) and got the following:-
SELECT  CONVERT(VARCHAR(10), transdt, 111) AS Transdt ,
amt
FROM Transcation
WHERE 1 = 1
AND Transdt BETWEEN CONVERT(VARCHAR(10), @transfrmdt, 111)
AND CONVERT(VARCHAR(10), @transtodt, 111)
SELECT name
FROM Mas_C
WHERE 1 = 1



I'm not sure if you wanted two separate queries, or if you wanted to have the second as a filter for name.

-------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63993 Visits: 17974
Two major issues with the way you are writing this. The first and the biggest issue is this is wide open to sql injection. You should NEVER directly execute a parameter. I realize that with the datatypes of the parameters you are not at great risk currently but this approach is extremely dangerous. You can and should parameterize your dynamic sql instead of building up a string and executing it.

Secondly you have the potential for some performance issues. This is a type of "catch all" query. Take a look at this post from Gail. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

She explains clearly how to deal with this type of query and properly parameterize the dynamic sql.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42374 Visits: 20012
You forgot to initialise @strqry to something other than null. Since it ultimately gets concatenated to everything, the end result is null.
You've got three completely separate queries in there. What are you trying to do?

How about posting what you expect to see from the PRINT statement?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ssurekha2000
ssurekha2000
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 207
i think query is unclear

Transcation tbl has transcationdate, amount and cid

Mas_C has cid and cname

Transcation date displayed will be from & todate passed from the application
1) the output needed is in all conditions

transcationdate, amount

if @<>type''
output shld be transcationdate, amount

if @cid<>''

output shld be transcationdate, amount cname
thava
thava
SSC-Addicted
SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)

Group: General Forum Members
Points: 499 Visits: 557
Please post your table structure and some sample data and what is your desired output



Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42374 Visits: 20012
ssurekha2000 (5/1/2014)
i think query is unclear

Transcation tbl has transcationdate, amount and cid

Mas_C has cid and cname

Transcation date displayed will be from & todate passed from the application
1) the output needed is in all conditions

transcationdate, amount

if @<>type''
output shld be transcationdate, amount

if @cid<>''

output shld be transcationdate, amount cname


It's unclear what you want to do. You almost certainly don't need dynamic sql. How many result sets are you expecting from this? Try to write the query(ies) without using dynamic sql and post back if it doesn't work.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ssurekha2000
ssurekha2000
SSC-Addicted
SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)SSC-Addicted (425 reputation)

Group: General Forum Members
Points: 425 Visits: 207
its not necessary to have dynamic sql
i had tried with normal query but didnot work so tried iwith dynamic sql

the out needed is display
1) date,amt with the given date range
2) conditional where clause ie if @type<>'' then
select * from tbl where date between @frmdt and @todt and type=@type
if @cid<>'' then
select * from tbl where date between @frmdt and @todt and cid=@cid

if both not blank

select * from tbl where date between @frmdt and @todt and cid=@cid and type=@type

and both blank then
select * from tbl where date between @frmdt and @todt
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search