SQL:what is wrong in following query

xodeblack

what is wrong in following query. datatype of @Name is text. @Name is parameter passing in stored procedure

select * from students
where first_name in ( + @Name + )

Blue_Boy

Which error message do you get?

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

xodeblack

Error: Msg 102, Level 15, State 1, Procedure GetFinancialInfo, Line 48 Incorrect syntax near ')'.

Sebastian Br

I would take a look on the SQL statement directly before executing.

Ashfield

You cannot use an IN with a variable. Either use dynamic sql or, my preferred method, use a function to split the list into a table variable and join on that. A quick google will give you examples, but here is a starter

CREATE FUNCTION [dbo].[fn_DelimitedSplit]
(
@TextToSplit VARCHAR(8000), @Delimiter VARCHAR(255)
)
RETURNS @SplitKeyword TABLE (Keyword VARCHAR(8000),seq int identity)
AS
BEGIN
DECLARE @Word VARCHAR(255)

WHILE (CHARINDEX(@Delimiter, @TextToSplit, 1)>0)
BEGIN
SET @Word = SUBSTRING(@TextToSplit, 1 , CHARINDEX(@Delimiter, @TextToSplit, 1) - 1)
SET @TextToSplit = SUBSTRING(@TextToSplit, CHARINDEX(@Delimiter, @TextToSplit, 1) + 1, LEN(@TextToSplit))
INSERT INTO @SplitKeyword(Keyword) VALUES(@Word)
END

INSERT INTO @SplitKeyword(Keyword) VALUES(@TextToSplit)
return
END

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP