[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Retrieving the value of A Column of Table1 whose name is obtained from Table2

Hi all. I have two tables. I need to get the value of a column of table1. The problem is that the name of that column is from table2. I will use these codes/select statements in my stored procedure. The error i get is "Syntax error converting the nvarchar value of @limitname to a column of data type int." How do i get the value of a column of a table with name located in another table. Please help. I really need it badly. Else, i will have to restructure the whole database. Please advise the easiest way to do this.

CREATE PROCEDURE spPOLLIvote (
@projectname as nvarchar(20),
@phonenumber as nvarchar(20)
)

AS
...
DECLARE @limitname as nvarchar(20)
DECLARE DECLARE @thedate as nvarchar(20)
DECLARE @limitvalue as int

SELECT @limitname= limitname FROM TABLE_profile WHERE  projectname = @projectname
SELECT @thedate = convert(nvarchar,thedate,101), @limitvalue = @limitname FROM TABLE_subscribers WHERE phonenumber=@phonenumber
...
GO
0
valerie14
Asked:
valerie14
  • 2
  • 2
1 Solution
 
lengreenCommented:
hi

I think it should be

CREATE PROCEDURE spPOLLIvote (
@projectname as nvarchar(20),
@phonenumber as nvarchar(20)
)

AS
...
DECLARE @limitname as nvarchar(20)
DECLARE DECLARE @thedate as nvarchar(20)
DECLARE @limitvalue as int

SELECT @limitname= limitname FROM TABLE_profile WHERE  projectname = @projectname
SELECT @thedate = convert(nvarchar,thedate,101), @limitvalue = limitvalue FROM TABLE_subscribers WHERE phonenumber=@phonenumber
...
GO

cheers

Len
0
 
HilaireCommented:
This can be achieved with dynamic SQL

CREATE PROCEDURE spPOLLIvote (
@projectname as nvarchar(20),
@phonenumber as nvarchar(20)
)
AS
DECLARE @limitname as nvarchar(20)
DECLARE @thedate as nvarchar(20)
DECLARE @limitvalue as int
DECLARE @SQL varchar(2000)

SELECT @limitname= limitname FROM TABLE_profile WHERE  projectname = @projectname
SET @SQL = N'SELECT @thedate = convert(nvarchar,thedate,101), @limitvalue = ' + @limitname+ ' FROM TABLE_subscribers WHERE phonenumber=@phonenumber'
exec sp_executeSQL @SQL, N'@thedate nvarchar(20) out, @limitvalue int out, @phonenumber nvarchar(20)', @thedate out, @limitvalue out, @phonenumber
select @thedate, @limitvalue
GO
0
 
ram2098Commented:
One way is ....You can do it using dynamic sqls and global temp tables....

CREATE PROCEDURE spPOLLIvote (
@projectname as nvarchar(20),
@phonenumber as nvarchar(20)
)

AS
...
DECLARE @limitname as nvarchar(20)
DECLARE DECLARE @thedate as nvarchar(20)
DECLARE @limitvalue as int
DECLARE @SQL VARCHAR(5000)

SELECT @limitname= limitname FROM TABLE_profile WHERE  projectname = @projectname
SELECT @sql = 'select convert(nvarchar,thedate,101) as date1, ' + @limitname + ' as col2 into ##temp1 FROM TABLE_subscribers WHERE phonenumber=' + @phonenumber

EXEC (@SQL)

SELECT @thedate = date1, @limitvalue = col2 FROM ##temp1
drop table ##temp1
GO




...If this is only one time query..this is fine..if you have to do it very frequently..I suggest to re-look at your schema.
0
 
HilaireCommented:
Dynamic SQL means you build the SQL statement at run time.
Unlike
EXEC(@strstring),
sp_executesql allows you to pass output parameters

Although it should work in your case, dynamic SQL has several caveats :
- dynamic SQL using EXEC forces recompiling of sql statement, so it's not very good performancewise
- dynamic SQL should be used with precautions since it allowes SQL injection : a hacker could pass an SQL string and get it executed on your server

Most of the time dynamic SQL can be avoided, but it means your less is less flexible
eg

--1) retrieve column name
SELECT @limitname= limitname FROM TABLE_profile WHERE  projectname = @projectname
--2) select a different field according to column name
SELECT @thedate = convert(nvarchar,thedate,101),
@limitvalue = case @limitname
      when 'column1' then column1
      when 'column2' then column2
      -- and so on ... you have to hardcode all possibilities, that's why it's less flexible
      else 'Not Found'
end as dynamicCol
FROM TABLE_subscribers WHERE phonenumber=@phonenumber

HTH

Hilaire
0
 
ram2098Commented:
Hilaire,

I don't know that we can pass input/output parameters from sp_executesql...

It is a good one.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now