Stored procedure help - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Stored procedure help

Author

Topic

Skydolphin
Starting Member

43 Posts

Posted - 2003-10-09 : 13:54:47

Hi. I found a script on the net a while back that allows you to parse a users input by , or whatever and then builds the correct select statement. See code posted below.

CREATE PROCEDURE dbo.GetLayerList
@SearchString VARCHAR(1000)
AS
BEGIN
DECLARE @SqlStr VARCHAR(6000)
DECLARE @SelectStr VARCHAR(1000)
DECLARE @WhereStr VARCHAR(500)
DECLARE @FromStr VARCHAR(1000)
DECLARE @OrderByStr VARCHAR(500)
DECLARE @keyword VARCHAR(100)
DECLARE @separator char(1)
DECLARE @separator_position int -- This is used to locate each separator character
SELECT @SearchString = Replace(@SearchString,' ',',')
SELECT @separator = ','
SELECT @SelectStr = "SELECT LayerName, LayerText, LayerTextType, LayerItemCount, ControlType, SubTopic "
SELECT @FromStr = " FROM [Layer_List]"
SELECT @OrderByStr = " order by LayerName, layerTextType"
set nocount on
set @SearchString = @SearchString + @separator
while patindex('%' + @separator + '%' , @SearchString) <> 0
begin
select @separator_position = patindex('%' + @separator + '%' , @SearchString)
select @keyword = left(@SearchString, @separator_position - 1)
If @WhereStr = ""
SELECT @WhereStr = "( LayerName like '%" + @keyword + "%' or LayerText like '%" + @keyword + "%' or Keywords like '%" + @keyword + "%' )"
Else
SELECT @WhereStr = @WhereStr + " And ( LayerName like '%" + @keyword + "%' or LayerText like '%" + @keyword + "%' or Keywords like '%" + @keyword + "%')"
-- This replaces what we just processed with and empty string
select @SearchString = stuff(@SearchString, 1, @separator_position, '')
end -- end while loop
set nocount off
SELECT @SqlStr = @SelectStr + @FromStr + @WhereStr + @OrderByStr
EXECUTE (@SqlStr)
END
GO

Anyway, I am getting an error cannot use empty object or column names. Use a single space if necessary.

Does anyone know what this means and where I should look. I am not a SQL pro so this is very frustrating.

Thanks
Rhonda

Rhonda

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-09 : 14:06:53

Query Analyzer will tell you the line number that is having the problem. When I copied your code into QA, it said error on line 24. You can then double click on the error to bring you to the line that is having the problem.

Anyway, change line 24 to this:

If @WhereStr = '' (two single quotes)

Instead of this:

If @WhereStr = "" (two double quotes)

Tara

Skydolphin
Starting Member

43 Posts

Posted - 2003-10-09 : 14:17:54

Wow Tara thanks.

I never knew you could run stored proc create code in QA. Still has other problems but at least I got passed that one.

Rhonda

Rhonda

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-09 : 14:22:08

What are you using to create the stored procedures? Enterprise Manager? EM will cause problems. Use Query Analyzer for T-SQL code.

Tara

Skydolphin
Starting Member

43 Posts

Posted - 2003-10-09 : 14:29:35

Yea I've always only used EM for sprocs.

I've run into another hurdle. It giving me an invalid column name on the lines.

SELECT @SelectStr = "SELECT LayerName, LayerText, LayerTextType, LayerItemCount, ControlType, SubTopic"
SELECT @FromStr = " FROM dbo.Layer_List"
SELECT @OrderByStr = " order by LayerName, layerTextType"

and anywhere else the column names are listed but if I run the query directly it is fine. Any ideas?

SELECT LayerName, LayerText, LayerTextType, LayerItemCount, ControlType, SubTopic FROM [Layer_List] WHERE LayerName like '%foo%' or LayerText like '%foo%' or Keywords like '%foo%' order by LayerName, layerTextType

Rhonda

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-09 : 14:37:27

Put some PRINT statements in your code (use QA for this). Check to see if the output looks correct:

CREATE PROCEDURE dbo.GetLayerList
@SearchString VARCHAR(1000)
AS
BEGIN
DECLARE @SqlStr VARCHAR(6000)
DECLARE @SelectStr VARCHAR(1000)
DECLARE @WhereStr VARCHAR(500)
DECLARE @FromStr VARCHAR(1000)
DECLARE @OrderByStr VARCHAR(500)
DECLARE @keyword VARCHAR(100)
DECLARE @separator char(1)
DECLARE @separator_position int -- This is used to locate each separator character
SELECT @SearchString = Replace(@SearchString,' ',',')
SELECT @separator = ','
SELECT @SelectStr = "SELECT LayerName, LayerText, LayerTextType, LayerItemCount, ControlType, SubTopic "
SELECT @FromStr = " FROM [Layer_List]"
SELECT @OrderByStr = " order by LayerName, layerTextType"
set nocount on
set @SearchString = @SearchString + @separator
while patindex('%' + @separator + '%' , @SearchString) <> 0
begin
select @separator_position = patindex('%' + @separator + '%' , @SearchString)
select @keyword = left(@SearchString, @separator_position - 1)
If @WhereStr = ""
SELECT @WhereStr = "( LayerName like '%" + @keyword + "%' or LayerText like '%" + @keyword + "%' or Keywords like '%" + @keyword + "%' )"
Else
SELECT @WhereStr = @WhereStr + " And ( LayerName like '%" + @keyword + "%' or LayerText like '%" + @keyword + "%' or Keywords like '%" + @keyword + "%')"
-- This replaces what we just processed with and empty string
select @SearchString = stuff(@SearchString, 1, @separator_position, '')
end -- end while loop
set nocount off
--SELECT @SqlStr = @SelectStr + @FromStr + @WhereStr + @OrderByStr
PRINT @SelectStr
PRINT @FromStr
PRINT @WhereStr
PRINT @OrderByStr
--EXECUTE (@SqlStr)
END
GO

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-09 : 14:41:51

Take a look at this thread...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830

And avoid dynamic sql...unless you can't

http://www.algonet.se/~sommar/dynamic_sql.html

Good luck

Brett

8-)

Skydolphin
Starting Member

43 Posts

Posted - 2003-10-09 : 15:11:24

Phew got that all figured out. Thanks everyone for you help.

Rhonda

Rhonda

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources