Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2006
    Posts
    72

    Unanswered: Stored procedures

    I'm using SQL server 2008 (if that matters)

    I've never written a stored procedure before and I'm not sure what they are meant for or capable of.

    There are a number of SQL statement that I would like to execute in a row in order to transform data into a fairly complicated form.

    I decided to start with the first SQL statement and see if I could get it to work in a procedure and I'm getting errors.

    The procedure as I currently have it written is below...

    Code:
    CREATE PROCEDURE TestProc
    	-- Add the parameters for the stored procedure here
    @InYears varchar(50),
    @SLevels varchar(50),
    @TS varchar(50),
    @ComputerName varchar(30)
    AS
    BEGIN
    	DECLARE @CompLocalVarTable varchar(30)
    	DECLARE @CompVarNames varchar(30)
    	Set @CompLocalVarTable = @ComputerName + 'LocalVarTable'
    	Set @CompVarNames = @ComputerName + 'VarNames'
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    	SELECT Varieties.VarietyID, Series.Year, Varieties.TestName, Samples.LabNumber, Samples.TrialID, Samples.VarID, Samples.[Level], Samples.SampleTypeID, Results.Results, Results.SampleID, Results.DoneYet, Results.ResultDetailID, ResultDetails.TraitCode, Samples.TrialID + Cast(Results.ResultDetailID AS VARCHAR(50)) AS TrialResultDetailID 
        INTO @CompLocalVarTable 
        FROM ((@CompVarNames INNER JOIN (Varieties INNER JOIN Samples ON Varieties.VarietyID = Samples.VarID) ON @CompVarNames.VarID = Varieties.VarietyID) INNER JOIN (ResultDetails INNER JOIN Results ON ResultDetails.ResultDetailID = Results.ResultDetailID) ON Samples.SampIDBarcode = Results.SampleID) INNER JOIN Series ON Samples.SeriesID = Series.SeriesID 
        WHERE @TS AND SampleTypeID=1 AND @SLevels AND @InYears
        -- Insert statements for procedure here	
    END
    GO
    It doesn't like that I am using variables in the FROM of the SQL statement. Maybe that is simply impossible and I'm barking up the wrong tree here.

    I'm going to try to work this out myself but any suggestions on how I might get the above procedure to work will be much appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    349
    table variables need to be in the scope of the statement or procedure.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    SELECT ... INTO only inserts rows into a table. What are you trying to assign to @CompLocalVarTable (currently defined as varchar(30))

    Is this supposed to return an output value, or just a recordset?

  4. #4
    Join Date
    Jun 2006
    Posts
    72
    I managed to get it to do what I wanted.

    What I needed to use was EXEC...

    Below is the working example.

    Code:
    CREATE PROCEDURE [dbo].[PCProc]
    @InYears varchar(500),
    @SLevels varchar(50),
    @TG varchar(50),
    @ComputerName varchar(30),
    @SRegions varchar(50)
    AS
    BEGIN
    	--DECLARE @CompLocalVarTable varchar(30)
    	--DECLARE @CompVarNames varchar(30)
    	--Set @CompLocalVarTable = @ComputerName + 'LocalVarTable'
    	--Set @CompVarNames = @ComputerName + 'VarNames'
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        EXEC ('SELECT Varieties.VarietyID, Series.Year, Varieties.TestName, Samples.LabNumber, Samples.TrialID, Samples.VarID, 
    			Samples.[Level], Samples.SampleTypeID, Results.Results, Results.SampleID, Results.DoneYet, Results.ResultDetailID, 
    			Results.TC AS TraitCode, Samples.TrialID + Cast(Results.ResultDetailID AS VARCHAR(50)) AS TrialResultDetailID 
    		INTO ' + @ComputerName + 'LocalVarTable
            FROM ' + @ComputerName + 'VarNames INNER JOIN 
    			Varieties INNER JOIN 
    			Samples ON Varieties.VarietyID = Samples.VarID ON ' + @ComputerName + 'VarNames.VarID = Varieties.VarietyID INNER JOIN 
    			Results ON Samples.SampIDBarcode = Results.SampleID INNER JOIN 
    			Series ON Samples.SeriesID = Series.SeriesID INNER JOIN 
    			TraitLists ON Results.TC = TraitLists.TraitCode 
            WHERE TraitGroupID=''' + @TG + ''' AND SampleTypeID=1 AND ' + @Slevels + ' AND ' + @InYears)
        
        EXEC ('SELECT Varieties.VarietyID, Series.Year, Varieties.TestName, Samples.LabNumber, Samples.TrialID, Samples.VarID, 
    			Samples.[Level], Samples.SampleTypeID, Results.Results, Results.SampleID, Results.DoneYet, Results.ResultDetailID, 
    			Results.TC AS TraitCode, Samples.TrialID + CAST(Results.ResultDetailID AS VARCHAR(50)) AS TrialResultDetailID 
    		INTO ' + @ComputerName + 'LocalConTable
    		FROM ' + @ComputerName + 'ConNames INNER JOIN
    			Varieties INNER JOIN
    			Samples ON Varieties.VarietyID = Samples.VarID ON ' + @ComputerName + 'ConNames.VarID = Varieties.VarietyID INNER JOIN
    			Results ON Samples.SampIDBarcode = Results.SampleID INNER JOIN
    			Series ON Samples.SeriesID = Series.SeriesID INNER JOIN
    			TraitLists ON Results.TC = TraitLists.TraitCode 
    		WHERE TraitGroupID=''' + @TG + ''' AND SampleTypeID=1 AND ' + @Slevels + ' AND ' + @InYears)
    
        EXEC ('SELECT DISTINCT 
    			Cons.VarietyID AS ConVarID, Vars.VarietyID AS VarVarID, Cons.Results AS ConResults, Vars.Results AS VarResults, 
                Cons.TestName AS ConTestName, Vars.TestName AS VarTestName, Vars.TraitCode, Vars.ResultDetailID, Vars.TrialID, Vars.Year 
    		INTO ' + @ComputerName + 'PC 
    		FROM ' + @ComputerName + 'LocalVarTable AS Vars INNER JOIN
                   ' + @ComputerName + 'LocalConTable AS Cons ON Vars.TrialResultDetailID = Cons.TrialResultDetailID')
    
        EXEC ('DELETE 
            FROM ' + @ComputerName + 'PC 
            WHERE EXISTS 
            (
            SELECT Trials.TrialID 
            FROM Trials 
            WHERE Trials.TrialID = ' + @ComputerName + 'PC.TrialID AND NOT (' + @SRegions + ')
            )')
    
        EXEC ('DELETE 
            FROM ' + @ComputerName + 'PC 
            WHERE ConResults Is Null OR VarResults Is Null')
    
        EXEC ('DROP TABLE ' + @ComputerName + 'LocalVarTable')
        
        EXEC ('DROP TABLE ' + @ComputerName + 'LocalConTable')
        
        EXEC ('DROP TABLE ' + @ComputerName + 'ConNames')
        
        EXEC ('DROP TABLE ' + @ComputerName + 'VarNames')
    END
    This is probably enough to explain exactly what I was trying to do.
    Last edited by Access Junkie; 05-28-09 at 03:46.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Oh, my. The biggest problem I have with this sort of procedure is what happens when two people try to run this procedure at the same time. Do you really have a separate set of tables for every computername? As you may have found out, it is causing you to bend over backwards coding around that design. I would consolidate all of these tables into one set, with ComputerName as a column.

  6. #6
    Join Date
    Jun 2006
    Posts
    72
    Quote Originally Posted by MCrowley
    Oh, my. The biggest problem I have with this sort of procedure is what happens when two people try to run this procedure at the same time. Do you really have a separate set of tables for every computername? As you may have found out, it is causing you to bend over backwards coding around that design. I would consolidate all of these tables into one set, with ComputerName as a column.
    I feel stupid that I haven't thought of this myself before, excellent suggestion .

    I'm actually in the process of rewriting the code now and I've found that I need to determine if a table exists.

    Can that be done by a procedure?

    Again I'll look into this myself and hopefully find the answer but again any help is always welcome.

  7. #7
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Quote Originally Posted by Access Junkie

    I'm actually in the process of rewriting the code now and I've found that I need to determine if a table exists.

    Can that be done by a procedure?

    Again I'll look into this myself and hopefully find the answer but again any help is always welcome.
    IF EXISTS (SELECT 1 FROM information_Schema.tables where TABLE_NAME = @TableNameVar)

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    another option that I frequent:
    Code:
    IF Object_ID('dbo.table_name') IS NOT NULL
      BEGIN
        PRINT 'table exists already!'
      END
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •