Tech Off Thread

4 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

T-SQL Synax Error

Back to Forum: Tech Off
  • User profile image
    W3bbo

    'lo

    I'm too tired to sort this one out Tongue Out

    Can anyone see what's wrong with it?

    (T-SQL for SQL Server 2000)

    
    CREATE PROCEDURE dbo.StatsGet
    
    
    AS
    
    
    CREATE TABLE #Stats 
    (
        [ID] bigint 
            IDENTITY(1,1)
             PRIMARY KEY,
        [Name] nvarchar(50),
        [Value] nvarchar(100),
        [SearchParams] nvarchar(150)
    )
    
    
    DECLARE @StatsResult table
    
    
    -- get total number of bugs
    
    
    SET @StatsResult = (SELECT count(*)  INTO [Stats] FROM [Items] WHERE [Type] = 0)
    
    
    INSERT INTO [Stats]
    (
        [Name], [Value], [SearchParams]
    ) VALUES
    (
        'Total bugs submitted ', @StatsResult, 'Foo'
    )
    
    
    -- get total number of unsolved bugs
    
    
    SET @StatsResult = (SELECT count(*)  INTO [Stats] FROM [Items] WHERE [Type] = 0) AND [Status] <> 3
    
    
    INSERT INTO [Stats]
    (
        [Name], [Value], [SearchParams]
    ) VALUES
    (
        'Total unresolved bugs ', @StatsResult, 'Bar'
    )
    
    
    -- get newest user
    
    
    SET @StatsResult = (SELECT TOP 1 [UserName]  INTO [Stats] FROM [Users] ORDER BY [ID] DESC
    
    
    INSERT INTO [Stats]
    (
        [Name], [Value], [SearchParams]
    ) VALUES
    (
        'Newest member ', @StatsResult, 'Baz'
    )
    

  • User profile image
    Maurits

    W3bbo wrote:
    DECLARE @StatsResult table

    -- get total number of bugs

    SET @StatsResult = (SELECT count(*)  INTO [Stats] FROM [Items] WHERE [Type] = 0)

    INSERT INTO [Stats]
    (
        [Name], [Value], [SearchParams]
    ) VALUES
    (
        'Total bugs submitted ', @StatsResult, 'Foo'
    )



    Instead:

    DECLARE @StatsResult numeric -- or int, I suppose, but certainly not Table

    SELECT
        @StatsResult = COUNT(*)
    FROM
        Items
    -- note no INTO

  • User profile image
    Minh

    A lot of these things you could do client-side... but I see the convinence of a SPROC if you don't do any data analysis....

    Also, remember temporary tables (#Stats) go away when the connection is closed, so how you called this SPROC from client-side code, could be problematic.... Consider simplifying your SPROC...

  • User profile image
    hurcane

    I don't understand why you're creating a temp table #Stats, but then using a permanent table [Stats] for your data. You also have a misplaced parenthesis on your second subquery.

    Is this your actual code, or did you type it in? Is the intent simply to get the value of the three summary queries back to the client? I'd write it like this:

    Create Procedure dbo.StatsGet
    @SubmittedBugCount int OUTPUT,
    @UnresolvedBugCount int OUTPUT,
    @NewestMember nvarchar(150) OUTPUT
    AS
    SELECT @SubmittedBugCount = Count(*) FROM [Items] WHERE [Type] = 0
    SELECT @UnresolvedBugCount = Count(*) FROM [Items] WHERE [Type] = 0 And [Status] <> 3
    SELECT TOP 1 @NewestMember = [UserName] FROM [Users] ORDER BY [ID] DESC
    RETURN

    This makes it very easy to use from the client. Alternatively, you could have the three variables as internal variables instead of parameters, and add this before the return statement:

    SELECT @SubmittedBugCount As SubmittedBugCount, @UnresolvedBugCount As UnresolvedBugCount, @NewestMember As NewestMember

    I prefer the output parameters, but sometimes a resultset it easier to use from the client perspective.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.