Feeding selections into a stored procedure

Hello All,

I'm not really sure where to post this as I'm not quite sure how to approach the problem; either whether it's an SQL problem or shoudl be addressed on the form.

Anyway, on an .aspx page (using VB.NET) I have a drop down box and two text boxes. The user first selects a centre from the drop down and then enters a start and end date in the two text boxes. The user then clicks on a button which lists the results of a SQL server stored procedure using the specified parameters in a datagrid. All this is fine and works.

Users however have requested an 'All centers' option in the drop down, which if selected, essentially means that instead of listing the results for a single centre, all centres are listed for the start and end dates specified.

This is where I'm having problems. How do I feed this into my stored procedure? Is it a change in the procedure or something I need to do on the form?

Any help appreciated.

Mo

P.S. stored procedure look like this:

------

@.centreid int,
@.startdate varchar(20),
@.enddate varchar(20)

AS

SELECT centreid, datecreated, centrename

FROM tblcentres

WHERE (DataLength(@.startdate) = 0 OR CentreID = @.CentreID)
AND (DataLength(@.startdate) = 0 OR datecreated >= @.startdate)
AND (DataLength(@.enddate) = 0 OR datecreated <= @.enddate)

I would pass a 0 or some other number like 9999 which is not in the list of centerId's and do a check in the stored proc. If its the 0 or 9999 you passed remove that from the where condition. Something like this:

IF @.centerId = 0SELECT centreid, datecreated, centrenameFROM tblcentresWHERE (DataLength(@.startdate) = 0OR datecreated >= @.startdate)AND (DataLength(@.enddate) = 0OR datecreated <= @.enddate)ELSE SELECT centreid, datecreated, centrenameFROM tblcentresWHERE (DataLength(@.startdate) = 0OR CentreID = @.CentreID)AND (DataLength(@.startdate) = 0OR datecreated >= @.startdate)AND (DataLength(@.enddate) = 0OR datecreated <= @.enddate)

|||

Is this part right:

WHERE (DataLength(@.startdate) = 0 OR CentreID = @.CentreID)?

Seems like you had the right idea for startdate/enddate, but you didn't implement it correctly for CentreID. You could change it to:

WHERE (@.CentreID=-1 OR CentreID = @.CentreID) then have the "All Centres" value -1. Or you can set the options value to a blank, have the sqldatasource convert blanks to nulls, and check for IS NULL. Any of those work.

I personally prefer to use the blank/null approach as most of the dropdown values I use are values from an identity column, and the field can not possibly be blank (It's marked not null). If it's not from an identity column, and is some kind of varchar field, then I'll simply use "All" as the value, and if someone put that into the database and they want to specifically search for it, they are sol.

|||Good catch Motley. I just cut n paste'ed the OP's query to give him/her an idea of how to approach the problem.|||Thanks to both of you for the advice and also for correcting the errors in my SQL.

Your suggestion(s) worked perfectly!

Mo

feeding results from one SP to anohter

Hi,
I need an SP to get the resultset output of another SP.
ie
create Procedure A
as
begin
select * from Area
end
Now in procedure B, how can I get these results into a cursor form processin
g?
Also, now to through another slant on it, what if Procedure A is in a remote
database, does the same logic apply?
Thanks,
Steve
(similar to a previous post today - sorry)One way to do would be to use global temporary tables (## prefix).
But this is more like a procedural programming approach which is more
suitable for client side as opposed to database side.
Could you please elaborate more on what you are trying to do.
Here is a sample code.
HTH...
use pubs
go
-- ========================================
=====
-- Create procedure basic template
-- ========================================
=====
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'mytestprocA'
AND type = 'P')
DROP PROCEDURE dbo.mytestprocA
GO
CREATE PROCEDURE dbo.mytestprocA
AS
drop table ##global_temp
select top 5 * into ##global_temp from authors
GO
-- ========================================
=====
-- example to execute the store procedure
-- ========================================
=====
EXECUTE dbo.mytestprocA
GO
use pubs
go
-- ========================================
=====
-- Create procedure basic template
-- ========================================
=====
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'mytestprocB'
AND type = 'P')
DROP PROCEDURE dbo.mytestprocB
GO
CREATE PROCEDURE dbo.mytestprocB
AS
DECLARE temp_Cursor CURSOR FOR
select * from ##global_temp
OPEN temp_Cursor
FETCH NEXT FROM temp_Cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM temp_Cursor
END
CLOSE temp_Cursor
DEALLOCATE temp_Cursor
GO
-- ========================================
=====
-- example to execute the store procedure
-- ========================================
=====
EXECUTE dbo.mytestprocB
GO
http://zulfiqar.typepad.com
BSEE, MCP
"Steve" wrote:

> Hi,
> I need an SP to get the resultset output of another SP.
> ie
> create Procedure A
> as
> begin
> select * from Area
> end
> Now in procedure B, how can I get these results into a cursor form process
ing?
> Also, now to through another slant on it, what if Procedure A is in a remo
te
> database, does the same logic apply?
> Thanks,
> Steve
> (similar to a previous post today - sorry)
>|||Best way to do this is change proc A to a function.
2nd best:
insert #table
Exec A
I would also ask what you are using the cursor for, but that is only because
cursors are evil :) Seriously most every use for a cursor has a far easier
to manage set based solution, and that is what we are here to help you with.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:8BA898FD-43B9-401C-83DB-BD314A2EAA19@.microsoft.com...
> Hi,
> I need an SP to get the resultset output of another SP.
> ie
> create Procedure A
> as
> begin
> select * from Area
> end
> Now in procedure B, how can I get these results into a cursor form
> processing?
> Also, now to through another slant on it, what if Procedure A is in a
> remote
> database, does the same logic apply?
> Thanks,
> Steve
> (similar to a previous post today - sorry)
>

Feeding an "IN" Clause

I know I can supply an explicit set to an IN clause, or I can feed it with a select statement. What other ways in TSQL 2005 can I feed it?

One thing I'd like to do is feed an IN clause by calling a stored procedure. I'm new to TSQL, so I don't know if I can do that. I'd also like to know if there is a way to pass a variadic list of values as a parameter to a stored procedure, so that they can be used in an "IN" clause found in that stored procedure. I think the way to do that last item is by having a recordset parameter. Is that correct? Any other way to do it?

You can use a static list of values for that:

IN (3,4,5)

HTh, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Yes, that syntax I already know, and is what I was calling an "explicit set." Its principally in the area of forming that set parametrically that I was after...such as through the "how to's" of stored procedures.|||

Based on another question you asked, you could try passing a comma delimited list of values to your stored procedure, and then use the LIKE functionality within a Case/When statement to ultimately achieve your goals.

Something like this...

Create Procedure IsValueIn(@.Value int, @.List VarChar(8000))
As
SET NOCOUNT ON

Select Case When ',' + @.List + ',' LIKE '%' + Convert(VarChar(20), @.Value) + '%'
Then 1
Else 0
End As ValueIn

Test it like this...

IsValueIn 10, '10,20,30'

IsValueIn 20, '10,20,30'

IsValueIn 30, '10,20,30'

IsValueIn 11, '10,20,30'

|||

My apologies for making a mistake. The method I presented in my previous post should work, but the implementation is a little off. The corrected code should be...

Alter Procedure IsValueIn(@.Value int, @.List VarChar(8000))
As
SET NOCOUNT ON

Select Case When ',' + @.List + ',' LIKE '%,' + Convert(VarChar(20), @.Value) + ',%'
Then 1
Else 0
End As ValueIn

Notice the extra commas added to the like compare. These commas are important because the code I posted earlier would return true for:

IsValueIn 1, '10,20,30'

With the code in this reply, the stored procedure correctly returns false. Again, my apologies for misleading you with my previous response.

|||

I would suggest using a user defined function. You can pass whatever you want, and break it up into a set of data that can be used like:

select *
from table
where tableId in (select value from dbo.myFunction(@.parameter))

A good resource for this sort of thing is Erland's article: http://www.sommarskog.se/arrays-in-sql.html

sql

Feeding "0"s in a string

hi,
I have this command in Vb that feeds "0" in a string, and I need it to work in a SP.

ProductNum = format(ProductNum, String(nBarCodelength, "0"))

if ProductNum = "12345" and nBarCodelength = 10
the result of the above will be "0000012345" (the numbers were fed with "0").

placing that command in a SP raises me an error.
What replaces 'format' and 'String' commands in TSQL?

ThanksTry:

declare @.string varchar(20)
select @.string = '12345'
select stuff(@.string,1,0,replicate('0',10 - len(@.string)))

or

declare @.string varchar(20)
select @.string = '12345'
select replicate('0',10 - len(@.string)) + @.string|||Originally posted by rnealejr
Try:

declare @.string varchar(20)
select @.string = '12345'
select stuff(@.string,1,0,replicate('0',10 - len(@.string)))

or

declare @.string varchar(20)
select @.string = '12345'
select replicate('0',10 - len(@.string)) + @.string

I will try it.
Many thanks mate.....