stored procedure problem......

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure problem......

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2008-11-25 : 12:18:35

Hello

I have a stored procedure,that will take CommentId as input,Each CommentID is associated with a CommentText,and i want to perform select statement where CommentText will be input.

here is my procedure,and this is not giving me any result

Alter PROCEDURE dbo.sp_MetatagSearch
( @MetatagID as varchar(300),

)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @srh as nvarchar(50)
SET @srh = 'select MetatagText from Metatag where MetatagID=@MetatagID'; .....this statement is not working.....

Select tm.SystemElementID,m.DocumentID,DocumentTitleText as "DocumentTitle", SETitleText as "TitleText"
From SystemElement s,MetaTag tm,TOCMap tp,TableOfContents tc,MasterDocument m
Where tm.SystemElementID=s.SystemElementID AND Freetext(MetaTagText,@srh)AND tm.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsActiveBit=1 AND tp.IsDeletedBit=0
)

please help thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 12:23:14

may be what you need is this


Alter PROCEDURE dbo.sp_MetatagSearch
( @MetatagID as varchar(300),

)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @srh as nvarchar(50)
SET @srh = 'select MetatagText from Metatag where MetatagID=' +@MetatagID; .....this statement is not working.....




Select tm.SystemElementID,m.DocumentID,DocumentTitleText as "DocumentTitle", SETitleText as "TitleText"
From SystemElement s,MetaTag tm,TOCMap tp,TableOfContents tc,MasterDocument m
Where tm.SystemElementID=s.SystemElementID AND Freetext(MetaTagText,@srh)AND tm.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsActiveBit=1 AND tp.IsDeletedBit=0
)

also dont use sp_ for sp names.

bluestar
Posting Yak Master

133 Posts

Posted - 2008-11-25 : 12:24:18

small correction MetatagID is int...by mistake I wrote that,still its not giving any result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 12:26:20

quote:
Originally posted by bluestar

small correction MetatagID is int...by mistake I wrote that,still its not giving any result

is it giving any error?

bluestar
Posting Yak Master

133 Posts

Posted - 2008-11-25 : 12:27:20

yes
Msg 245, Level 16, State 1, Procedure sp_MetatagSearch, Line 17
Conversion failed when converting the varchar value 'select MetatagText from Metatag where MetatagID=' to data type int.

Please do reply
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 12:30:05

quote:
Originally posted by bluestar

yes
Msg 245, Level 16, State 1, Procedure sp_MetatagSearch, Line 17
Conversion failed when converting the varchar value 'select MetatagText from Metatag where MetatagID=' to data type int.

Please do reply
Thanks


Alter PROCEDURE dbo.MetatagSearch
( @MetatagID as int

)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @srh as nvarchar(50)
SET @srh = 'select MetatagText from Metatag where MetatagID=' +CAST(@MetatagID AS varchar(20)); .....this statement is not working.....




Select tm.SystemElementID,m.DocumentID,DocumentTitleText as "DocumentTitle", SETitleText as "TitleText"
From SystemElement s,MetaTag tm,TOCMap tp,TableOfContents tc,MasterDocument m
Where tm.SystemElementID=s.SystemElementID AND Freetext(MetaTagText,@srh)AND tm.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsActiveBit=1 AND tp.IsDeletedBit=0
)

as told earlier please dont use sp_ for sp names.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 12:32:15

see the reason here

http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html

bluestar
Posting Yak Master

133 Posts

Posted - 2008-11-25 : 12:41:10

now that error is gone,but still I am not getting any result...I don't understand why
and regarding sp_,all the stored procedures are written using sp_ as prefix in this entire project,I am new to this ,and I dont wont to go against their system.I know you are right,but please ignore it,as I have no choice,but to use it.

bluestar
Posting Yak Master

133 Posts

Posted - 2008-11-25 : 12:45:31

Also If I am executing this query by entering metatag text manually,its giving me results,
Select tm.SystemElementID,m.DocumentID,DocumentTitleText as "DocumentTitle", SETitleText as "TitleText"
From SystemElement s,MetaTag tm,TOCMap tp,TableOfContents tc,MasterDocument m
Where tm.SystemElementID=s.SystemElementID AND Freetext(MetaTagText,'Ameta1')AND tm.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsActiveBit=1 AND tp.IsDeletedBit=0

1321 278 mydoc Initial system element
1321 278 mydoc Initial system element
1514 289 mynewdoc apple

but I will have to get metatagtext from the id,please help.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-11-25 : 12:50:30

Instead of trying to use dynamic sql and a variable, try joining on the Metatag table instead.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

bluestar
Posting Yak Master

133 Posts

Posted - 2008-11-25 : 12:55:01

well I am not using dynamic sql,in my procedure This statement SET @srh = 'select MetatagText from Metatag where MetatagID=' +CAST(@MetatagID AS varchar(20)) is not working I think,thats the reason I am not getting any result.how to fix it????????
will appreciate any help
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 13:06:01

quote:
Originally posted by bluestar

Also If I am executing this query by entering metatag text manually,its giving me results,
Select tm.SystemElementID,m.DocumentID,DocumentTitleText as "DocumentTitle", SETitleText as "TitleText"
From SystemElement s,MetaTag tm,TOCMap tp,TableOfContents tc,MasterDocument m
Where tm.SystemElementID=s.SystemElementID AND Freetext(MetaTagText,'Ameta1')AND tm.SystemElementID=tp.SystemElementID AND tp.TableOfContentsID=tc.TableOfContentsID AND tc.DocumentID=m.DocumentID AND SEIsActiveBit=1 AND tp.IsDeletedBit=0

1321 278 mydoc Initial system element
1321 278 mydoc Initial system element
1514 289 mynewdoc apple

but I will have to get metatagtext from the id,please help.

where did you get this value from? is it result of first query?

bluestar
Posting Yak Master

133 Posts

Posted - 2008-11-25 : 13:17:31

no I just manually entered the value 'Ameta1' to see if my query is giving any result.And it is,This 'Ameta1' value I should get from SET @srh = 'select MetatagText from Metatag where MetatagID=' +CAST(@MetatagID AS varchar(20)), for iD = 67 say ,which I am not getting.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 13:23:43

quote:
Originally posted by bluestar

no I just manually entered the value 'Ameta1' to see if my query is giving any result.And it is,This 'Ameta1' value I should get from SET @srh = 'select MetatagText from Metatag where MetatagID=' +CAST(@MetatagID AS varchar(20)), for iD = 67 say ,which I am not getting.

Aha..i thought you want to pass string as it is.
as per your current explanation what you want is this (you could specified this before)


Alter PROCEDURE dbo.MetatagSearch
( @MetatagID as int

)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @srh as nvarchar(1000)
SELECT  @srh =MetatagText from Metatag where MetatagID= @MetatagID 



Select tm.SystemElementID,
m.DocumentID,
DocumentTitleText as DocumentTitle, 
SETitleText as TitleText
From SystemElement s
JOIN MetaTag tm
ON tm.SystemElementID=s.SystemElementID
JOIN TOCMap tp
ON tm.SystemElementID=tp.SystemElementID
JOIN TableOfContents tc
ON tp.TableOfContentsID=tc.TableOfContentsID 
JOIN MasterDocument m
ON tc.DocumentID=m.DocumentID
Where  Freetext(MetaTagText,@srh)
AND SEIsActiveBit=1 
AND tp.IsDeletedBit=0

ALso
1. dont use "" for aliases
2.Use ANSI join syntax as above as this old syntax wont be supported in future versions of SQL Server

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources