Refrence Input Param-Brain Farting I suppose

Question

Post reply

Refrence Input Param-Brain Farting I suppose

wilson-213647

SSC Enthusiast

Points: 112
More actions
February 14, 2006 at 12:11 pm

#167103

Well Okay...

dbo.SEARCH
(
@TYPE  int,                      /*Type is used for dressing the variables*/
@F_NM varchar(30),
@L_NM  varchar(30),
@ZIP  varchar(10)
&nbsp
AS
IF(@TYPE='1')
BEGIN
IF(@F_NM <>'')
BEGIN
   @F_NM=@F_NM + '%'
END

It keeps erroring here..
Line 19: Incorrect syntax near '@F_NM'.

How do I reference the incoming parameter

Thanks in advance for help

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

JeffB Hall of Fame Points: 3541 More actions · Answer 1

You need to use either

SELECT @F_NM=@F_NM + '%'

OR

SET @F_NM=@F_NM + '%'

wilson-213647 SSC Enthusiast Points: 112 More actions · Answer 2

Thanks....nice....worked great...I wasn't brain farting...I just didn't know.

Did I say thanks yet....

anyways...THANKS

bledu SSChampion Points: 12335 More actions · Answer 3

if you don't mind my saying, looking at your code i don't think you will achieve what you want because you have put wild character string [%] at the end.

what do you want to do?

Everything you can imagine is real.

JeffB Hall of Fame Points: 3541 More actions · Answer 4

How can you say that based upon the code snippet provided?

This could very will work if via some frontend application, a @TYPE of 1 is passed in to signify the search should be made using 'BEGINS WITH' searches. I use this exact method where 1 = 'BEGINS WITH', 2 = 'ENDS WITH' and 3 = 'CONTAINS'

For @Type 1 :

Set @Var = @Var + '%'

For @Type 2:

Set @Var = '%' + @Var

And for @Type 3:

Set @Var = '%' + @Var + '%'

wilson-213647 SSC Enthusiast Points: 112 More actions · Answer 5

That was exactly what I am doing...Ok..So I have a new problem..

PAGINATION....

I want to bring in a dynamic limit actually two!

@MYLIMIT int, /*Input parameter

SELECT * FROM(SELECT TOP @MYLIMIT PARAMETER FROM(SELECT @LIMITVALUE PARAMETER

Getting error:

Incorrect syntax near '@MYLIMIT'.

How do I reference that variable @MYLIMIT

WTF!

JeffB Hall of Fame Points: 3541 More actions · Answer 6

You can't. You will have to do something like:

declare @sql varchar(8000)

set @sql = 'select top ' + cast(@MYLIMT as varchar) + ' * from ......'

exec(@SQL)

wilson-213647 SSC Enthusiast Points: 112 More actions · Answer 7

Wow...so...Do you have a recomendation on how to do pagination in a stored proc. I have done it using PHP with the select statements in the PHP script, but I am trying to do this all with StoredProcs.

I will have to keep exploring I suppose.

Thanks for your replies..Jeff

PW-201837 SSC-Insane Points: 20805 More actions · Answer 8

>>Do you have a recomendation on how to do pagination in a stored proc.

It depends on what you have available, in terms of data and version of Sql Server.

If you're on SQL2005, you can use the new T-SQL RANKing functions to assign row numbers to your resultset to assist with pagination.

On older versions, you can place the resultset into a temporary table with an IDENTITY column to provide sequential row numbers to assist with paging ... but of course this incurs overhead in building the temp table before selecting each page from it.

wilson-213647 SSC Enthusiast Points: 112 More actions · Answer 9

Okay, is there way to create a stored proc that you can then call in a sql select statement.

For example:

proc name: sp_FooBar

SELECT * FROM sp_FooBar;

When I try to do this I get the following:

Ivalid Object Name: sp_FooBar

Thanks

PW-201837 SSC-Insane Points: 20805 More actions · Answer 10

You have to declare a table or temp table that matches the columns in the resultset, then use INSERT INTO.

eg:

CREATE TABLE #Temp ( {Your column list goes here} )

INSERT INTO #Temp

EXEC sp_FooBar

SELECT * FROM #Temp

Alternatively, you create the temp table, and change your proc to populate the temp table instead of returning a resultset. Then select out of the temp table once the proc call has completed.

wilson-213647 SSC Enthusiast Points: 112 More actions · Answer 11

If you have multiple users how do you keep the result set seperate? Does it use sessions?

JeffB Hall of Fame Points: 3541 More actions · Answer 12

I would use a user defined function that returns a table as in:

create function fnTable (@var varchar(20))

returns @t table (

Reference varchar(100)

)

as

begin

insert into @t

SELECT @Var + '-1'

UNION

SELECT @Var + '-2'

UNION

SELECT @Var + '-3'

return

end

go

select * from dbo.fnTable('aaaaa')

JeffB Hall of Fame Points: 3541 More actions · Answer 13

When SQL creates the temp tables, each connection gets its own copy of a #TEMP table. If you look in sysobjects after creating a #TEMP table, the name will be something like '#TEMP___________________________________000000000003B' where the end part is unique to each connection.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 14

Behind the scenes, any table prefixed with '#' is decorated with additional connection /session data to make it unique.

If you are concerned about scope, you could also use a table-type variable, which only has scope within the executing batch:

eg:

DECLARE @Temp TABLE

( {Column List} )

INSERT INTO @Temp

EXEC usp_FooBar

SELECT * FROM @Temp