retrive all records within the Case statment

i want to filter a table by a view using a defind function
for example:
select * from person
where dbo.person.company like case CmpName()
when 'all' then '%'
else CmpName()
end
prblem is that '%' doesn't show the records with NULL value
how can i
thanks
samTry using IS NULL clause
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Uwa Agbonile[MSFT]
"Sam" <focus10@.zahav.net.il> wrote in message
news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> i want to filter a table by a view using a defind function
> for example:
> select * from person
> where dbo.person.company like case CmpName()
> when 'all' then '%'
> else CmpName()
> end
> prblem is that '%' doesn't show the records with NULL value
> how can i
> thanks
> sam
>|||"IS NULL" is not working with "=" , or "Like" operators
so i can not use it with the "CASE" statment
"Uwa Agbonile [MSFT]" <uwaag@.online.microsoft.com> wrote in message
news:ez3NxhwYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Try using IS NULL clause
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Regards,
> Uwa Agbonile[MSFT]
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
>

retrive all records within the Case statment

i want to filter a table by a view using a defind function
for example:
select * from person
where dbo.person.company like case CmpName()
when 'all' then '%'
else CmpName()
end
prblem is that '%' doesn't show the records with NULL value
how can i
thanks
sam
Try using IS NULL clause
This posting is provided "AS IS" with no warranties, and confers no rights.
Regards,
Uwa Agbonile[MSFT]
"Sam" <focus10@.zahav.net.il> wrote in message
news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> i want to filter a table by a view using a defind function
> for example:
> select * from person
> where dbo.person.company like case CmpName()
> when 'all' then '%'
> else CmpName()
> end
> prblem is that '%' doesn't show the records with NULL value
> how can i
> thanks
> sam
>
|||"IS NULL" is not working with "=" , or "Like" operators
so i can not use it with the "CASE" statment
"Uwa Agbonile [MSFT]" <uwaag@.online.microsoft.com> wrote in message
news:ez3NxhwYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Try using IS NULL clause
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Regards,
> Uwa Agbonile[MSFT]
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:uWyStcqYFHA.3364@.TK2MSFTNGP12.phx.gbl...
>
sql

Retrieving multiple Result Sets from within a stored procedure

A coworker of mine came to me with a question about a stored procedure he's working on. In this case, he has a stored procedure which invokes a second stored procedure. He wants that second stored procedure to return two result sets and then make use of both of them in the original stored procedure.

I know how to make a stored procedure return multiple result sets (just do two select statements), but how would you utilize those from the original stored procedure?

Thanks.

You can put the resultset in a temporary table with

INSERT INTO #TempTableName
EXEC SPName

or use a userdefined function rather than a stored procedure if applicable.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Right - that works great as long as the stored procedure being invoked only returns a single result set. How do you manage if it returns two result sets? That's what I'm most curious about.
|||

You could put the results in unique named global temporary tables. Passing the name to the procedure which then inserts the results and which can be then later used in the outer procedure.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

You could go the global temp route, but I think thats a ridiculous solution to what you have described. Multiple resultsets can be leveraged via stored procs, but usually through ADO (.nextResultset I believe). Dropping these into global temps is just a little overboard for me.
I would recommend just writing the second resultset in its own procedure, or using some variable to indicate which resultset you desire and executing it twice from the parent proc. Or, if theyre identical schemas, just return them with an additional column to indicate the original resultset.

Global temp should be last resort.

Heres an example of what I mean by using an additional column:

--
-- Create / populate 2 tables
--
create table dbo.A (Col1 varchar(10))
go
create table dbo.B (Col1 varchar(10))
go
insert into dbo.A
select 'TableA'
insert into dbo.B
select 'TableB'
go

--
-- Create Child proc that returns 2 resultsets
--
alter procedure [dbo].[p_Child]
as
set nocount on

select *, 'A' from dbo.A
select *, 'B' from dbo.B
go

--
-- Parent proc to call Child
--
alter procedure dbo.p_Parent
as
set nocount on

create table #temp (Col1 varchar(10), Location char(1))

insert into #temp
exec dbo.p_Child

-- return resultsets
select * from #temp where Location = 'A'
select * from #temp where Location = 'B'

drop table #temp
go

--
-- Execute parent to see multiple resultsets returned
--
exec dbo.p_Parent

--
-- cleanup
--
-- drop procedure dbo.p_Child
-- drop procedure dbo.p_Parent
-- drop table dbo.A
-- drop table dbo.B

|||No problem having multiple results sets of the same structure. There will be all the records in temp table as if they combined by union all, and error when recordsets are different.

RETRIEVE VALUE AFTER ADD FROM SQLDATASOURCE

I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC

Thanks

Steve

protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e){if (e.Exception ==null){int id = Convert.ToInt32(e.Command.Parameters["@.ProductId"].Value);}}

Sure, you just need to add a Parameter of the type "Return" as in this example:

ASPX

<asp:formview id="FormView1" runat="server" datasourceid="SqlDataSource1" defaultmode="Insert"><insertitemtemplate>Name:<asp:textbox id="NameTextBox" runat="server" text='<%# Bind("Name")%>' /><br /><asp:linkbutton id="InsertButton" runat="server" causesvalidation="True" commandname="Insert"text="Insert" /><asp:linkbutton id="InsertCancelButton" runat="server" causesvalidation="False" commandname="Cancel"text="Cancel" /></insertitemtemplate><itemtemplate>Id:<asp:label id="IdLabel" runat="server" text='<%# Eval("Id")%>' /><br />Name:<asp:label id="NameLabel" runat="server" text='<%# Bind("Name")%>' /><br /><asp:linkbutton id="NewButton" runat="server" causesvalidation="False" commandname="New"text="New" /></itemtemplate></asp:formview>

CODE-BEHIND

protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e){if (e.Exception ==null){int id = Convert.ToInt32(e.Command.Parameters["@.ProductId"].Value);}}