dynamic where prob

hi!

i can't seem to get this right...

create procedure PRstudentInfo

(

@.pStudentCode varchar(20) = NULL,

@.pHidden bit

)

as

begin

declare @.select varchar(max), @.where varchar(max)

set @.select =

'select b.name, c.studentcode from basicinfo b inner join fullinfo c on b.code = c.studentcode'

set @.where =

'WHERE isnull(c.studentcode,'''') ' + 'like ' + '''%' + isnull(@.pStudentCode,'') + '%''' + ' and ' +

' isnull(c.hidden,'''') = ''' + isnull(@.pHidden,'') '' --> here is the error

exec(@.select + @.where)

end

--

the error message is :

Incorrect syntax near ''

i also tried this:

' isnull(c.confidential,'''') = ''' + isnull(@.pConfidential,'') + ' '

The data types varchar and bit are incompatible in the add operator.

A tip : add the following code line:

print @.select + @.where , to see how sql string is made.

I suppose you have to put a space :

'hereWHERE isnull(c.studentcode,'''') etc

|||

Change it to this:

' isnull(c.hidden,'''') = ''' + cast(isnull(@.pHidden,'') as char(1))

|||WOuld be nice if you can describe the next time, what you are trying to do within your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de