Execute and sp_executesql 1

[eveCalypso]

Dear All,

I am delving into dynamic stored procedures. I think what I want to do is pretty straight forward. I have 5 possible paramters of which only 2 will always be present (dates).
So, for example:
@anIDColumn int
@toDate datetime
@fromDate datetime
@anInvoiceID int
@BinID char(12)

I want a dynamic stored procedure which will decide which parameters are needed based on what comes into the procedure.
I learnt about EXEC @sqlStr
which is a String I can build, but because I need to convert all the fields to varChar(x), my dates are in the wrong format and the query fails!!

So I then tried sp_executeSql, but I can only set up the SQL statement once - which means that, for example, excluding the invoice number OR setting the invoice to null or setting it to a specific value requires different SQL wording every time - based on the incoming value...

Does anyone have any idea what I could do to make this selection work?? What I am doing is asking the user for search inputs to narrow down which transactions they want to see for an Edit. (between dates, specific invoice, specific item etc).

Any advice would be appreciated.
Regards,
EvE

 

[philhege]

eveCalypso,

consider this code:

declare @l_vSQLString varchar(2048),
@l_vparamdate varchar(12)
set @l_vparamdate = '01/12/2004'
set @l_vSQLString =

'SELECT * FROM Mytable WHERE thedate = '''+@l_vparamdate+'''
ORDER BY ID'

exec(@l_vSQLString)

SQL Server is pretty flexible with date parsing, so if you pass it a date STRING, as demonstrated above, the query should work.

HTH,



Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[eveCalypso]

I hall try that and let you know! Thank you very much!
Regards,
EvE

 

[zhavic]

Also you can use 'sp_executesql' stored procedure,
and than you don't need to convert dates to strings.

Example:

SET @l_vSQLString = 'SELECT * FROM Mytable WHERE thedate = @l_vparamdate AND some_column = @anIDColumn ORDER BY [ID]'
EXECUTE [purple]sp_executesql[/purple] @l_vSQLString,
N'@l_vparamdate datetime,@anIDColumn int', -- list of variables used is query
@l_vparamdate, -- pass variables listed above
@anIDColumn

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[eveCalypso]

I know, but then i have the problem i described above!
rgds,
eve

 

[zhavic]

I have read more from your post, and it may be done without using dynamic SQL.


[blue]SELECT[/blue] *
[blue]FROM[/blue] your_table
[blue]WHERE[/blue] ( @anIDColumn [blue]IS NULL[/blue] OR some_IDColumn = @anIDColumn )
AND
( @toDate [blue]IS NULL[/blue] OR [blue]CONVERT[/blue]( char(8), toDateColumn, 112 ) <= @toDate )
AND
( @fromDate [blue]IS NULL[/blue] OR [blue]CONVERT[/blue]( char(8), fromDateColumn, 112 ) >= @fromDate )
AND
( @anInvoiceID [blue]IS NULL[/blue] OR InvoiceID_Column = @anInvoiceID )
AND
( @BinID [blue]IS NULL[/blue] OR BinID_Column = @BinID )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[eveCalypso]

Dear Zhavic,

Not quite
The options my end user have are as follows:
"All" or specific ItemID (i.e. if specific ItemID has not been selected, do not let it affect rest of selection)
Between dates (this is fine)
"All" or specific invoice (i.e. if specific invoiceID not given, do not let it affect selection)
Same with BinID

In fact, for the invoice, I can have
Invoice = null (outgoing stock)
Invoice = 0 ("All" invoices)
Invoice = x (Specific invoice)

In which case the above won't work.
I have been hunting for a good solution!
Thank you so much for your feedback so far.

Regards,
EvE

 

[zhavic]

Don't know how you indentify outgoing stock,
but may be something like this:

...
AND
( ( @anInvoiceID IS NULL AND InvoiceID_Column IS NULL ) OR @anInvoiceID = 0 OR InvoiceID_Column = @anInvoiceID )
AND
...

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[eveCalypso]

This is what I originally wanted to do, but couldn't get it working. This looks much better though! I thought a solution through SQL was possible. I shall give it a try tomorrow. Thank you so much for your help so far.

Best Regards,
EvE

 

[eveCalypso]

Dear Zhavic,

Thank you for your help - this is exactly what I was looking for !
Regards,
EvE