swati agrawal

swati agrawal

  • NA
  • 20
  • 98k

Store Procedure not working

Apr 5 2011 1:37 AM
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


Answers (4)