Hi all i have a storeprocedure dat is given below:-
USE [tklight]
GO
/****** Object: StoredProcedure [offshore].[Search] Script Date: 04/05/2011 10:10:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [offshore].[Search]
@General varchar(50)
AS
BEGIN
declare @tbl1 as table (name varchar(50),Id int,discription varchar(100),profilePic varchar(50),comments varchar(50),DateProfileCreate varchar(50),email varchar(50),Category varchar(50),country varchar(50),TypeU varchar(50))
declare @countArtist int
declare @countVenue int
set @countArtist =(select count(*) from tbl_artists where tbl_artists.name like '%@General%' or discription like '%@General%')
set @countVenue=(select count(*) from tbl_venues where tbl_venues.name like '%@General%' or discription like '%@General%')
IF @countArtist >0
BEGIN
insert into @tbl1 SELECT tbl_artists.name,tbl_artists.ArtistId as Id,tbl_artists.discription, tbl_artists.profilePic, tbl_artists.comments,convert(varchar(50),tbl_artists.DateProfileCreate,105)as DateProfileCreate, tbl_artists.email,tbl_artists.Category,tbl_artists.country,(tbl_artists.Type) as TypeU FROM tbl_artists where tbl_artists.name like '%@General%' or discription like '%@General%'
END
else IF @countVenue >0
BEGIN
insert into @tbl1 SELECT tbl_venues.name,tbl_venues.venueId,tbl_venues.discription, tbl_venues.profilePic, tbl_venues.comments,convert(varchar(50),tbl_venues.DateProfileCreate,105)as Date, tbl_venues.email,tbl_venues.Category,tbl_venues.country,tbl_venues.Type FROM tbl_venues where tbl_venues.name like '%@General%' or discription like '%@General%'
END
else IF @countArtist > 0 And @countVenue >0
BEGIN
insert into @tbl1
SELECT tbl_artists.name,tbl_artists.ArtistId,tbl_artists.discription, tbl_artists.profilePic, tbl_artists.comments,convert(varchar(50),tbl_artists.DateProfileCreate,105)as Date, tbl_artists.email,tbl_artists.Category,tbl_artists.country,tbl_artists.Type FROM tbl_artists where tbl_artists.name like '%@General%' or discription like '%@General%'
union
SELECT tbl_venues.name,tbl_venues.venueId,tbl_venues.discription, tbl_venues.profilePic, tbl_venues.comments,convert(varchar(50),tbl_venues.DateProfileCreate,105)as Date, tbl_venues.email,tbl_venues.Category,tbl_venues.country,tbl_venues.Type FROM tbl_venues where tbl_venues.name like '%@General%' or discription like '%@General%'
END
select * from @tbl1
END
when m executing dis sp it showing me table without values(mean balnk table).prob is dat when m execute queries which i use in dis sp giving correct result bt not in sp. m i doing smthng wrong in sp.plz help me