Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

forming a dynamic query Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 2:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, 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
Post #1566653
Posted Thursday, May 1, 2014 3:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 3,085, Visits: 3,281
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 question

There 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
Post #1566656
Posted Thursday, May 1, 2014 3:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, Visits: 207
swap what?
i am not getting any results
Post #1566658
Posted Thursday, May 1, 2014 3:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 3,085, Visits: 3,281
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 question

There 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
Post #1566660
Posted Thursday, May 1, 2014 7:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 13,455, Visits: 12,318
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 Moden's 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)
Post #1566713
Posted Thursday, May 1, 2014 7:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Post #1566715
Posted Thursday, May 1, 2014 10:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, 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
Post #1566792
Posted Thursday, May 1, 2014 6:59 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 12:30 AM
Points: 253, Visits: 540
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
Post #1566898
Posted Friday, May 2, 2014 2:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Post #1566950
Posted Saturday, May 3, 2014 11:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:04 AM
Points: 79, 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
Post #1567281
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse