TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
Reply
Answers (
4
)
modifying column type take too much time
SQL Group By Error