Refrence Input Param-Brain Farting I suppose
-
Well Okay...
dbo.SEARCH
(
@TYPE int, /*Type is used for dressing the variables*/
@F_NM varchar(30),
@L_NM varchar(30),
@ZIP varchar(10)
 
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
-
You need to use either
SELECT @F_NM=@F_NM + '%'
OR
SET @F_NM=@F_NM + '%'
-
Thanks....nice....worked great...I wasn't brain farting...I just didn't know.
Did I say thanks yet....
anyways...THANKS
-
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 + '%'
-
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!
-
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)
-
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
-
>>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.
-
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
-
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.
-
If you have multiple users how do you keep the result set seperate? Does it use sessions?
-
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')
-
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.
-
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply