DataGrid, DataAdapter, SP Parameters?

[James]

I have an sp with paramaters like this:
ALTER PROCEDURE dbo.SomeProc
(
@SortBy varchar(50) = NULL
@ShowComplete bit = 0
@StaffID int = NULL
)
AS
DECLARE @SQL varchar(2500)
SET @SQL =
'SELECT StaffID, Status FROM SomeTable '

IF @StaffID IS NOT NULL
SET @SQL = @SQL + ' WHERE StaffID = ' + @StaffID
ELSE
SET @SQL = @SQL + ' WHERE SQLLogin = SUSER_SNAME() '
IF @ShowComplete <> 1
SET @SQL = @SQL + ' AND StatusID NOT IN(3,4,5) '
IF @SortBy IS NOT NULL
SET @SQL = @SQL + ' ORDER BY ' + @SortBy

--PRINT @SQL
EXEC (@SQL)

I am trying to create a dataadapter (daTasks) bound to this sp, & then bind
my datagrid (dgdTasks) to it.

This works perfectly, until I include the parameters in the sp. The
parameters all have default values, but when I add these parameters to the
parameters collection of daTasks, I get a host of errors, such as 'Failed to
get schema for this stored procedure' & 'Input string was not in correct
format.'

For information, I can execute the sp from the IDE with or without
parameters, without a problem.

Any suggestions as to how I can solve this?

 

[Michael Tkachev]

Hi,

Don't use sqlstring. you have a lot of errors there.
The bellow you can find my solution, but you have to make a sort in the C#
or VB.

if @showcomlete = 1
begin
SELECT StaffID, Status FROM SomeTable
where StaffID = isnull(@StaffID, StaffID )
and SQLLogin =
case
when @StaffID is null then SUSER_SNAME()
else SQLLogin
end
end
else
begin
SELECT StaffID, Status FROM SomeTable
where StaffID = isnull(@StaffID, StaffID )
and SQLLogin =
case
when @StaffID is null then SUSER_SNAME()
else SQLLogin
end
end

bye