Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Whats wrong with this sproc?

Author  Topic 

ronnieoverby
Starting Member

36 Posts
Posted - 2008-12-08 : 13:05:19
It compiles fine and everything but I never get a result set, all I get is Command(s) completed successfully.

Here is the sproc:

ALTER PROCEDURE [Roster].[SearchRoster] 
	-- Add the parameters for the stored procedure here
	@SearchText nvarchar(100) = '',
	@Status nvarchar(15) = null,
	@Department nvarchar(100) = null,
	@Division nvarchar(100) = null,
	@SortExpression nvarchar(50) = 'ColleagueID',
	@PageIndex int = 1,
	@PageSize int = 9999999
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

declare @first nvarchar(10), @last nvarchar(10);
set @first = convert(nvarchar(10),(@PageIndex - 1) * @PageSize + 1)
set @last = convert(nvarchar(10),@PageIndex * @PageSize)

declare @sql nvarchar(max);

set @sql = 'WITH OrderedRoster AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY '+@SortExpression+') AS ''RowNumber''
    FROM Roster.ViewRoster 
	WHERE (@Department IS NULL OR Department = '+@Department+') AND
            (@Division IS NULL OR Division = '+@Division+') AND
            (@Status IS NULL OR Status = '+@Status+') AND
            (@SearchText IS NULL OR (FirstName LIKE ''%' + @SearchText + '%'') OR
                                    (LastName LIKE ''%' + @SearchText + '%'') OR
                                    (Title LIKE ''%' + @SearchText + '%''))
) 
    SELECT * 
    FROM OrderedRoster 
    WHERE (RowNumber BETWEEN '+@first+' AND '+@last+')'


EXEC(@sql)
END



This worked just fine before I turned it into Dynamic sql (to achieve the dynamic sorting).Even if I type garbage into the sql string (example: aksdjfokasdjfiojasodif), the stored procedure will execute but not return results or an error.Does anyone see my folly?

ronnieoverby
Starting Member

36 Posts
Posted - 2008-12-08 : 13:12:23
Changed my sproc but still have problem.

ALTER PROCEDURE [Roster].[SearchRoster] 
	-- Add the parameters for the stored procedure here
	@SearchText nvarchar(100) = '',
	@Status nvarchar(15) = null,
	@Department nvarchar(100) = null,
	@Division nvarchar(100) = null,
	@SortExpression nvarchar(50) = 'ColleagueID',
	@PageIndex int = 1,
	@PageSize int = 9999999
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

declare @first nvarchar(10), @last nvarchar(10);
set @first = convert(nvarchar(10),(@PageIndex - 1) * @PageSize + 1)
set @last = convert(nvarchar(10),@PageIndex * @PageSize)

declare @sql nvarchar(max);

set @sql = 'WITH OrderedRoster AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY '+@SortExpression+') AS ''RowNumber''
    FROM Roster.ViewRoster 
	WHERE ('+@Department+' IS NULL OR Department = '+@Department+') AND
            ('+@Division+' IS NULL OR Division = '+@Division+') AND
            ('+@Status+' IS NULL OR Status = '+@Status+') AND
            ('+@SearchText+' IS NULL OR (FirstName LIKE ''%' + @SearchText + '%'') OR
                                    (LastName LIKE ''%' + @SearchText + '%'') OR
                                    (Title LIKE ''%' + @SearchText + '%''))
) 
    SELECT * 
    FROM OrderedRoster 
    WHERE (RowNumber BETWEEN '+@first+' AND '+@last+')'


EXEC(@sql)
END
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-08 : 13:16:56
I hope you don't tell anyone you are using dyanmic sql with user supplied values for a search procedure.
You are this close to get attacked by SQL Injection...


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

ronnieoverby
Starting Member

36 Posts
Posted - 2008-12-08 : 13:23:14
Alright, so what's wrong with the sproc?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-08 : 14:18:14
Scope.
ALTER PROCEDURE Roster.SearchRoster
(
	@SearchText nvarchar(100) = '',
	@Status nvarchar(15) = null,
	@Department nvarchar(100) = null,
	@Division nvarchar(100) = null,
	@SortExpression nvarchar(50) = 'ColleagueID',
	@PageIndex int = 1,
	@PageSize int = 9999999
)
AS

SET NOCOUNT ON;

declare	@first nvarchar(10),
	@last nvarchar(10),
	@sql nvarchar(max)

select	@first = convert(nvarchar(10),(@PageIndex - 1) * @PageSize + 1),
	@last = convert(nvarchar(10), @PageIndex * @PageSize)

set @sql = 'WITH OrderedRoster AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@SortExpression) + ') AS RowNumber
    FROM Roster.ViewRoster 
	WHERE Department LIKE ' + QUOTENAME(COALESCE(@Department, '%'), '''') + '
	AND   Division LIKE ' + QUOTENAME(COALESCE(@Division, '%'), '''') + '
	AND   Status LIKE ' + QUOTENAME(COALESCE(@Status, '%'), '''') + '
	AND   (FirstName LIKE ' + QUOTENAME('%' + COALESCE(@SearchText, '') + '%', '''') + ' OR LastName LIKE ' + QUOTENAME('%' + COALESCE(@SearchText, '') + '%', '''') + ' OR Title LIKE ' + QUOTENAME('%' + COALESCE(@SearchText, '') + '%', '''') + ')
) 
    SELECT	* 
    FROM	OrderedRoster 
    WHERE	RowNumber BETWEEN ' + @first + ' AND ' + @last + ')'

EXEC(@sql)


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

ronnieoverby
Starting Member

36 Posts
Posted - 2008-12-08 : 14:23:18
Error when executing your code:

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ')'.

Can you tell me what your changes do?
Go to Top of Page

ronnieoverby
Starting Member

36 Posts
Posted - 2008-12-08 : 17:04:23
I ended up writing a sproc in c#:

Look good?

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SearchRoster(
        SqlString SearchText,
        SqlString Status,
        SqlString Department,
        SqlString Division,
        SqlString SortExpression,
        SqlInt32 PageIndex,
        SqlInt32 PageSize,
        SqlBoolean Debug)
    {
        // do not debug if true was not passed.
        if (Debug.IsNull) Debug = false;

        // calculate records to retrieve based on page size and page index
        if (PageIndex.IsNull) PageIndex = 1;
        if (PageSize.IsNull) PageSize = SqlInt32.MaxValue;
        SqlInt32 first = (PageIndex - 1) * PageSize + 1;
        SqlInt32 last = PageIndex * PageSize;

        // build query
        SqlCommand cmd = new SqlCommand();
        StringBuilder query = new StringBuilder(@"
            ;WITH OrderedRoster AS
            (
                SELECT *, ROW_NUMBER() OVER (ORDER BY {SortExpression}) AS RowNumber
                FROM Roster.ViewRoster
                WHERE 1 = 1 ");

        if (!Department.IsNull)
        {
            query.Append(" AND Department = @Department ");
            cmd.Parameters.AddWithValue("@Department", Department);
        }

        if (!Division.IsNull)
        {
            query.Append(" AND Division = @Division ");
            cmd.Parameters.AddWithValue("@Division", Division);
        }

        if (!Status.IsNull)
        {
            query.Append(" AND Status = @Status ");
            cmd.Parameters.AddWithValue("@Status", Status);
        }

        if (!SearchText.IsNull)
        {
            query.Append(@"
                AND ((FirstName LIKE '%' + @SearchText + '%') 
                OR (LastName LIKE '%' + @SearchText + '%') 
                OR (Title LIKE '%' + @SearchText + '%')) ");
            cmd.Parameters.AddWithValue("@SearchText", SearchText);
        }

        query.Append(@"
            )
            SELECT * 
            FROM OrderedRoster 
            WHERE (RowNumber BETWEEN @first AND @last)");

        cmd.Parameters.AddWithValue("@first", first);
        cmd.Parameters.AddWithValue("@last", last);

        // set sort expression if none was provided
        if (SortExpression.IsNull)
        {
            SortExpression = "ColleagueID";
        }
        query.Replace("{SortExpression}", SortExpression.ToString());

        // create connection and execute command
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();

            if (Debug)
            {
                SqlContext.Pipe.Send(query.ToString());
            }

            cmd.CommandText = query.ToString();
            cmd.Connection = conn;
            SqlContext.Pipe.ExecuteAndSend(cmd);
        }

    }
};
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-08 : 18:07:19
Remove the paranthesis to the right of the "@last" parameter.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-08 : 18:07:42
[code]ALTER PROCEDURE Roster.SearchRoster
(
@SearchText nvarchar(100) = '',
@Status nvarchar(15) = null,
@Department nvarchar(100) = null,
@Division nvarchar(100) = null,
@SortExpression nvarchar(50) = 'ColleagueID',
@PageIndex int = 1,
@PageSize int = 9999999
)
AS

SET NOCOUNT ON;

declare @first nvarchar(10),
@last nvarchar(10),
@sql nvarchar(max)

select @first = convert(nvarchar(10),(@PageIndex - 1) * @PageSize + 1),
@last = convert(nvarchar(10), @PageIndex * @PageSize)

set @sql = 'WITH OrderedRoster AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@SortExpression) + ') AS RowNumber
FROM Roster.ViewRoster
WHERE Department LIKE ' + QUOTENAME(COALESCE(@Department, '%'), '''') + '
AND Division LIKE ' + QUOTENAME(COALESCE(@Division, '%'), '''') + '
AND Status LIKE ' + QUOTENAME(COALESCE(@Status, '%'), '''') + '
AND (FirstName LIKE ' + QUOTENAME('%' + COALESCE(@SearchText, '') + '%', '''') + ' OR LastName LIKE ' + QUOTENAME('%' + COALESCE(@SearchText, '') + '%', '''') + ' OR Title LIKE ' + QUOTENAME('%' + COALESCE(@SearchText, '') + '%', '''') + ')
)
SELECT *
FROM OrderedRoster
WHERE RowNumber BETWEEN ' + @first + ' AND ' + @last

EXEC(@sql)[/code]

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

ronnieoverby
Starting Member

36 Posts
Posted - 2008-12-09 : 08:16:28
That's ok, I'm not going to use a tsql sproc for this. I found this awesome article that helped me:

http://www.sommarskog.se/dyn-search-2005.html
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-09 : 09:44:25
How will it help you dynamically change column name for sort?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

ronnieoverby
Starting Member

36 Posts
Posted - 2008-12-09 : 10:23:34
I am handling the sort with query.Replace():


// build query
        SqlCommand cmd = new SqlCommand();
        StringBuilder query = new StringBuilder(@"
            ;WITH OrderedRoster AS
            (
                SELECT *, ROW_NUMBER() OVER (ORDER BY {SortExpression}) AS RowNumber
                FROM Roster.ViewRoster
                WHERE 1 = 1 ");

        if (!Department.IsNull)
        {
            query.Append(" AND Department = @Department ");
            cmd.Parameters.AddWithValue("@Department", Department);
        }

        if (!Division.IsNull)
        {
            query.Append(" AND Division = @Division ");
            cmd.Parameters.AddWithValue("@Division", Division);
        }

        if (!Status.IsNull)
        {
            query.Append(" AND Status = @Status ");
            cmd.Parameters.AddWithValue("@Status", Status);
        }

        if (!SearchText.IsNull)
        {
            query.Append(@"
                AND ((FirstName LIKE '%' + @SearchText + '%') 
                OR (LastName LIKE '%' + @SearchText + '%') 
                OR (Title LIKE '%' + @SearchText + '%')) ");
            cmd.Parameters.AddWithValue("@SearchText", SearchText);
        }

        query.Append(@"
            )
            SELECT * 
            FROM OrderedRoster 
            WHERE (RowNumber BETWEEN @first AND @last)");

        cmd.Parameters.AddWithValue("@first", first);
        cmd.Parameters.AddWithValue("@last", last);

        // set sort expression if none was provided
        if (SortExpression.IsNull)
        {
            SortExpression = "ColleagueID";
        }
        query.Replace("{SortExpression}", SortExpression.ToString());
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-09 : 10:29:48
And that is different from post 12/08/2008 : 18:07:42 in what way?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site