www.webdeveloper.com
Results 1 to 4 of 4

Thread: Is it possible to assign a column to a variable in SQL Server?

  1. #1
    Join Date
    Jul 2004
    Posts
    43

    Is it possible to assign a column to a variable in SQL Server?

    That is, in a stored procedure, I want to use 1 stored procedure to select from a table based on the result of any column the user chooses.

    i.e.


    declare @someColumn as nvarchar (don't know if there's another datatype for this)
    declare @value as nvarchar
    set @someColumn = someIDkey

    select *
    from myDB
    where @someColumn = @value

    Suppose I had a database that kept track of login info. I want to have 1 stored procedure instead of multiple ones that would only get info based on the userID, username, password, login time, etc.

    In short, I'd like to combine these (hypothetically - not my actual database I'm using) into 1 stored procedure. Is it possible?

    select *
    from myDB
    where userID = @value

    select *
    from myDB
    where userName = @value

    select *
    from myDB
    where loginTime = @value

    select *
    from myDB
    where lastAction = @value

  2. #2
    Join Date
    Feb 2003
    Posts
    2,745
    Code:
    Create Proc dbo.usp_myProc
      @col varchar(255),
      @value varchar(50)
    
    AS
    
    Exec('
      SELECT someFields
      FROM   myTable
      WHERE ' + @col + ' = ''' + @value + '''
    )
    GO

  3. #3
    Join Date
    Feb 2003
    Posts
    2,745
    as for the 2nd part of your question
    Code:
    Create Proc dbo.usp_myProc
      @userId int = null,
      @userName varchar(32) = null,
      @loginTime smalldatetime = null,
      @lastAction int = null
    
    AS
    
    Declare @sql varchar(1250)
    Declare @where varchar(1250)
    SET @sql = 'SELECT someFields FROM myTable '
    SET @where = 'WHERE 1=1 '
    
    IF @userId is NOT NULL
      SET @where = @where + 'AND userId = ' + cast(@userId as varchar(32)
    IF @userName is NOT NULL
      SET @where = @where + ' AND userName = ''' + @userName + ''''
    
    -- etc.
    SET @sql = @sql + @where
    Exec (@sql)

  4. #4
    Join Date
    Feb 2003
    Posts
    2,745
    You could also just execute SQL based on the condition:
    Code:
    IF @userName is NOT NULL
    BEGIN
      SELECT * FROM myTable WHERE userNAme = @userName
      Return
    END
    
    IF @userId is NOT NULL
    BEGIN
      SELECT * FROM myTable WHERE userId = @userId
      Return
    END
    That said, I'd still create several SPs. You'll get better performance, and you'll encapsulate good code that does one job well into it's own SP for easy re-use.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles