April 19, 2012 at 8:43 am
hi
i have an sql proc which i'm calling from excel
i want to pass in an account number as a variable which is fine. but i also want to pass in the operator. so either account no = 12345 OR account no <> 12345
can i pass the operator as a variable??
thanks
April 19, 2012 at 10:32 am
yes
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2012 at 11:38 am
Yes. You might want to elaborate if you want more info.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 20, 2012 at 1:36 am
i'm trying something like...
myproc (@operator varchar(7), @value varchar(25))
as
select col1, col2 from tbl1
where col1 @operator @value
is this kind of dynamic deceleration possible??
thanks
April 20, 2012 at 3:10 am
spin (4/20/2012)
i'm trying something like...
myproc (@operator varchar(7), @value varchar(25))
as
select col1, col2 from tbl1
where col1 @operator @value
is this kind of dynamic deceleration possible??
thanks
Try something more like this: -
CREATE myproc (@operator VARCHAR(7), @value VARCHAR(25)) AS
BEGIN
DECLARE @SQL AS NVARCHAR(MAX);
IF @operator IN ('=', '<>')
BEGIN
SET @SQL = 'SELECT col1, col2' + CHAR(13) + CHAR(10) + 'FROM tbl1' + CHAR(13) + CHAR(10) + 'WHERE col1 ' +
@operator + ' @Dvalue';
EXECUTE sp_executesql @SQL, N'@Dvalue VARCHAR(25)', @Dvalue = @value;
END
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply