Sql query into a Procedure with Union

[krotha]

Hi
I have a sql query which I want to make it as a stored procedure. How shall use Union operation in the stored procedure.

Here is My query.

select state_id owner_id,'state_id' owner_type
from campus
where site_id='7001'
and state_id is not null
union
select district_id owner_id,'district_id' owner_type
from campus
where site_id='7001'
and district_id is not null

Thanks for your help.

 

[grahamm]

Just create a sql script and run query analyser or isql.
To create a parameterless procedure foo with your select, the script would be:

create procedure foo
as
select state_id owner_id,'state_id' owner_type
from campus
where site_id='7001'
and state_id is not null
union
select district_id owner_id,'district_id' owner_type
from campus
where site_id='7001'
and district_id is not null

GO

If you want to make site_id a parameter for example, then

create procedure foo(@site_id int)
as
select state_id owner_id,'state_id' owner_type
from campus
where site_id=@site_id
and state_id is not null
union
select district_id owner_id,'district_id' owner_type
from campus
where site_id=@site_id
and district_id is not null

GO

Look for create procedure (and drop procedure) in SQL Server BOL

 

[krotha]

Thanks a lot.

 

[krotha]

How shall I return the values owner_id,owner_type. I am calling this procedure from VB and I am not getting the output values for owner_id and owner_type.

Thanks.

I created the procedure like this
CREATE procedure location_owner
@table_name nvarchar(255),
@pk_fld_id nvarchar(255),
@pk_value nvarchar(255),
@owner_id nvarchar(255) output,
@owner_type nvarchar(255) output
as
declare @sql nvarchar(4000)
set @sql = 'select state_id owner_id,''state_id'' owner_type from ' +@table_name+ ' where '+@pk_fld_id+ '='''+@pk_value+''' and state_id is not null
union select district_id owner_id,''district_id'' owner_type from ' +@table_name+ ' where '+@pk_fld_id+ '='''+@pk_value+''' and district_id is not null
Execute(@sql)
return @owner_id
return @owner_type
GO

 

[grahamm]

Before I answer, a question: Are you sure the select will only return one row? Ie if district_id is NULL then state_id is NOT NULL. I ask because your logic appears to be assuming this.