Use VBA to update a Stored Procedure? 2

[sabloomer]

I am not sure the best way to achieve my goal, so I am open to suggestions. I am converting a *.MDB into a project. I have a form that has three combo boxes that the user uses to make criteria selections. I know how to save the query as a Stored Procedure and to use the report to pass the Stored Procedure a parameter.

My problem is that I want to pass optional parameters. In Access I would use create a field like

IIf(IsNull([Forms]![frm_qbe]![cmb_IREF1]),True,[ITEMS]![ITEM_REF1]=[Forms]![frm_qbe]![cmb_IREF1]) AS MyField

with the criteria set to TRUE.

If the user left one of the combo boxes blank, it was not used in the criteria selection. I can't figure out how to pass a parameter to a Stored Procedure, and if it is NULL, not to include that in the WHERE clause.

My first idea is to use VBA to alter the Stored Procedure and then call it. Is that a good idea? How do I do that?

Thanks,

sabloomer


a

 

[cmmrfrds]

In the SP, you could dynamically buld the sql.

Example of building dynamic SQL.

Alter Procedure sp_customerOrders
(
@id as varchar(12),
@begdate varchar(12),
@enddate varchar(12)
)
As
declare @sql varchar(2000)
declare @sql1 varchar(500)
declare @sql2 varchar(500)

set @id = 'alfki'

-- select * from customers a, orders b
-- where a.customerid = b.customerid
-- and a.customerid = @id

set @sql1 = 'select * from customers a, orders b ' +
'where a.customerid = b.customerid ' +
'and a.customerid = '

-- Use 4 tick marks to surround a string variable.

set @sql = @sql1 + '''' + @id + ''''
-- select @sql

exec (@sql)

return

A SECOND WAY TO CREATE DYNAMIC SQL. This feeds the parameter to a function called executesql, which allows parameters to substitute in a String. This avoids the problem with surrounding with quotes on a varchar field.

Alter Procedure sp_customerOrders2
(
@id as varchar(12),
@begdate varchar(12),
@enddate varchar(12)
)
As
declare @sql nvarchar(2000)
declare @sql1 nvarchar(500)
declare @sql2 nvarchar(500)

set @id = 'alfki'

set @sql1 = 'select * from customers a, orders b ' +
'where a.customerid = b.customerid ' +
'and a.customerid = @id1'

set @sql = @sql1

declare @ParmDefinition nvarchar(500)
SET @ParmDefinition = N'@id1 nvarchar(12)'

EXECUTE sp_executesql @sql, @ParmDefinition, @id1 = @id

return

You can use if logic to build different sql strings.
For example.
If @id1 is null
Begin
@sql1 = 'select ........'
Else
@sql1 = 'select different where logic ......
End

 

[sabloomer]

Thank you, I thought that there was a way to do that. I will give that a try and let you know if I could get that to work.

Thanks Again,

sabloomer

 

[sabloomer]

Can you tell me what I am doing wrong? I always return no records.

ALTER PROCEDURE QBE2
(
@ItemNumber as varchar(12),
@CustomerID as varchar(4)
)

AS

Declare @sqlMain varchar(250)
Declare @sqlWhere varchar(250)
Declare @sqlFinal varchar(500)


--SELECT TRAN_MONTH, CUST_ID, ITEM, QTY, DOLLARS, DATA_TYPE
--FROM dbo.CDS_HISTORY
--WHERE (TRAN_MONTH = 1) AND (ITEM = N'ABC123') AND (CUST_ID = N'0144')


set @sqlMain = 'SELECT TRAN_MONTH, CUST_ID, ITEM, QTY, DOLLARS, DATA_TYPE ' +
'FROM dbo.CDS_HISTORY '

IF (@ItemNumber) = NULL
IF (@sqlWhere) = NULL
SET @sqlWhere = 'WHERE (ITEM = N' + '''' + @ItemNumber + '''' + ')'
ELSE
SET @sqlWhere = @sqlWhere + ' AND (ITEM =N' + '''' + @ItemNumber + '''' + ')'

IF (@CustomerID) = NULL
IF (@sqlWhere) = NULL
SET @sqlWhere = 'WHERE (CUST_ID=N' + '''' + @CustomerID+ '''' + ')'
ELSE
SET @sqlWhere = @sqlWhere + ' AND (CUST_ID=N' + '''' + @CustomerID+ '''' + ')'


SET @sqlFinal = @sqlMain + @sqlWhere

EXECUTE (@sqlFinal)

 

[sabloomer]

Opps, I found one mistake...

IF (@ItemNumber) = NULL
should be
IF (@ItemNumber) <> NULL

I made the change and still get no records. Is there anyway to "debug" a stored procedure? I can't figure out what it is really doing. I tried the "Print" option, but I can't figure out how to read the "Printed" information.

Thanks,

sabloomer

 

[cmmrfrds]

Sorry, I by mistake left comments in my code. In sql server transact SQL a comment is either /* some comment */
or
-- some comment

Take out the --

 

[danvlas]

I'd not go for dynamic SQL if it's possible another way.

In your stored proc, create the condition like:

Where Field = Case When @Parameter Is Null Then Field Else @Parameter End

and it should work fine.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[cmmrfrds]

What danvlas suggests should work in your case, but in the case of dynamic sql.

In order to see what you built in the stored procedure put the sql in a variable and return it in a select statement then you can look at it in the app and see if the syntax is correct.



SET @sqlFinal = @sqlMain + @sqlWhere

Select @sqlFinal

 

[sabloomer]

First, Thank you for all of the help. It was a big help. I was able to get the dynamic SQL to work after some of the changes were made. One simple change was that I needed to change "IF (@ItemNumber) = NULL" to "IF (@ItemNumber) is NULL". That drove me crazy. I had alwasys tested isnull(@ItemNumber) = true, something to get used to.

danvlas,
I am a little confused by your post. I don't understand how to apply your example to my sample. If the variable is null then I don't want any consideration for that field. I just can't "see" it.

Thanks again everyone!

sabloomer

 

[danvlas]

Where Field = Case When @Parameter Is Null Then Field Else @Parameter End

If the parameter is null, then compare the field value to the field value (like 1=1)
Else, compare the field value to the parameter.



HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[sabloomer]

danvlas,

Thanks for the clarification. That helped clear it up for me.

Thanks,

sabloomer

 

[sabloomer]

danvlas,

I hope you could help me with a related question. Each order in the table can have a status between 1 and 6. I want the user to be able to select multiple statuses, or none at all to get all. Any suggestions?