Prev: generate summary row question
Next: SQL 2008 intellisense errors Invalid Object
From: MS Forum Newgroup User on 14 Nov 2009 11:13
I'm creating a tempval, from this value I use it in the next select query
but I receive an error:

Conversion failed when converting the varchar value 'Select ColumnID1 from
dbo.tbl_main where id = ' to data type int.

However if I hard code the

Set @tempval = 'Select ColumnID1 from dbo.tbl_main where id = 1'

this works?


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[GetUserDetails]
@ID int,
@IDName varchar(100)

as
begin

DECLARE @tempval varchar(100)

Set @tempval = 'Select ' + @IDName + ' from dbo.tbl_main where id = ' +
@ID

Select isnull(username,'NA') AS UserName, isnull(useremail,'NA')AS
EmailAddress
from tbl_login
where tbl_login.designation = isnull(@tempval,'NA')

end




From: Dan Guzman on 14 Nov 2009 12:12
> Set @tempval = 'Select ' + @IDName + ' from dbo.tbl_main where id = ' +
> @ID

The '+' operator in SQL Server is used for both addition and concatenation.
Integer has a higher data type precedence that varchar so SQL Server thinks
you are trying to perform an arithmetic add operation on the query string.
The conversion of the query string to integer fails as the error message
indicates.

It looks to me like your intention is to execute a dynamic query and use the
returned scalar value in the second query. You need to explicitly execute
the statement, which I suggest you use a parameterized query to do so like
the untested example below. See http://www.sommarskog.se/dynamic_sql.html
for important dynamic SQL considerations.

ALTER proc [dbo].[GetUserDetails]
@ID int,
@IDName varchar(100)
AS
BEGIN

DECLARE
@sql_statement nvarchar(MAX),
@tempval varchar(100);

SET @sql_statement = N'SELECT ' + @IDName + N' FROM dbo.tbl_main where id =
@ID';

EXEC sp_executesql
@sql_statement,
N'@ID int, @tempval varchar(100) OUTPUT',
@ID = @ID,
@tempval = @tempval OUTPUT;

SELECT
ISNULL(username,'NA') AS UserName,
ISNULL(useremail,'NA')AS EmailAddress
FROM dbo.tbl_login
WHERE tbl_login.designation = ISNULL(@tempval,'NA');

RETURN @@ERROR;

END
GO



--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


"MS Forum Newgroup User" <euser(a)microsoft.com> wrote in message
news:uQU30VUZKHA.2160(a)TK2MSFTNGP02.phx.gbl...
> I'm creating a tempval, from this value I use it in the next select query
> but I receive an error:
>
> Conversion failed when converting the varchar value 'Select ColumnID1 from
> dbo.tbl_main where id = ' to data type int.
>
> However if I hard code the
>
> Set @tempval = 'Select ColumnID1 from dbo.tbl_main where id = 1'
>
> this works?
>
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> ALTER proc [dbo].[GetUserDetails]
> @ID int,
> @IDName varchar(100)
>
> as
> begin
>
> DECLARE @tempval varchar(100)
>
> Set @tempval = 'Select ' + @IDName + ' from dbo.tbl_main where id = ' +
> @ID
>
> Select isnull(username,'NA') AS UserName, isnull(useremail,'NA')AS
> EmailAddress
> from tbl_login
> where tbl_login.designation = isnull(@tempval,'NA')
>
> end
>
>
>
>
From: MS Forum Newgroup User on 14 Nov 2009 14:28
Dan,

Thanks for the link, it provides great information and I will go through
this with depth.

Thanks for this, the value @tempval = @tempval OUTPUT is correct but it
seems that this does not get passed correctly to the next query as it display
a wrong value.

Thanks for your help.

"Dan Guzman" wrote:

> > Set @tempval = 'Select ' + @IDName + ' from dbo.tbl_main where id = ' +
> > @ID
>
> The '+' operator in SQL Server is used for both addition and concatenation.
> Integer has a higher data type precedence that varchar so SQL Server thinks
> you are trying to perform an arithmetic add operation on the query string.
> The conversion of the query string to integer fails as the error message
> indicates.
>
> It looks to me like your intention is to execute a dynamic query and use the
> returned scalar value in the second query. You need to explicitly execute
> the statement, which I suggest you use a parameterized query to do so like
> the untested example below. See http://www.sommarskog.se/dynamic_sql.html
> for important dynamic SQL considerations.
>
> ALTER proc [dbo].[GetUserDetails]
> @ID int,
> @IDName varchar(100)
> AS
> BEGIN
>
> DECLARE
> @sql_statement nvarchar(MAX),
> @tempval varchar(100);
>
> SET @sql_statement = N'SELECT ' + @IDName + N' FROM dbo.tbl_main where id =
> @ID';
>
> EXEC sp_executesql
> @sql_statement,
> N'@ID int, @tempval varchar(100) OUTPUT',
> @ID = @ID,
> @tempval = @tempval OUTPUT;
>
> SELECT
> ISNULL(username,'NA') AS UserName,
> ISNULL(useremail,'NA')AS EmailAddress
> FROM dbo.tbl_login
> WHERE tbl_login.designation = ISNULL(@tempval,'NA');
>
> RETURN @@ERROR;
>
> END
> GO
>
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
>
> "MS Forum Newgroup User" <euser(a)microsoft.com> wrote in message
> news:uQU30VUZKHA.2160(a)TK2MSFTNGP02.phx.gbl...
> > I'm creating a tempval, from this value I use it in the next select query
> > but I receive an error:
> >
> > Conversion failed when converting the varchar value 'Select ColumnID1 from
> > dbo.tbl_main where id = ' to data type int.
> >
> > However if I hard code the
> >
> > Set @tempval = 'Select ColumnID1 from dbo.tbl_main where id = 1'
> >
> > this works?
> >
> >
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > ALTER proc [dbo].[GetUserDetails]
> > @ID int,
> > @IDName varchar(100)
> >
> > as
> > begin
> >
> > DECLARE @tempval varchar(100)
> >
> > Set @tempval = 'Select ' + @IDName + ' from dbo.tbl_main where id = ' +
> > @ID
> >
> > Select isnull(username,'NA') AS UserName, isnull(useremail,'NA')AS
> > EmailAddress
> > from tbl_login
> > where tbl_login.designation = isnull(@tempval,'NA')
> >
> > end
> >
> >
> >
> >
> .
>
From: Erland Sommarskog on 14 Nov 2009 16:31
Dan Guzman (guzmanda(a)nospam-online.sbcglobal.net) writes:
> ALTER proc [dbo].[GetUserDetails]
> @ID int,
> @IDName varchar(100)
> AS
> BEGIN
>
> DECLARE
> @sql_statement nvarchar(MAX),
> @tempval varchar(100);
>
> SET @sql_statement = N'SELECT ' + @IDName + N' FROM dbo.tbl_main where id
> = @ID';
>
> EXEC sp_executesql
> @sql_statement,
> N'@ID int, @tempval varchar(100) OUTPUT',
> @ID = @ID,
> @tempval = @tempval OUTPUT;

But @tmpval does not appear anywhere in the query?

It is not clear to me what "MS Forum Newgroup User" want to achieve. I
just posted an answer to an older question in .newusers where there was
no indication of wanting to use dynamic SQL.

This was the query I suggested in that group:

Select l.username AS UName, isnull(l.useremail,'NA') AS UEmail
from tbl_login l
join tbl_main m ON m.column_name = l.designation
where tbl_login.designation = @ID


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: MS Forum Newgroup User on 14 Nov 2009 16:50
Erland,

Apologies for the cross post, I was not sure about where to post my query.

You are correct about a join, but I could not get this working hence I tried
to split this up into 2.

As in the example:
tbl_main:
ID, ColumnID1, ColumnID2, ColumnID3, ColumnID4
1, AA, AB, AC,
AD
2, AE, AF, AG,
AH

tbl_login:
designation, uername, useremail
AA, username1, useremail1
AB, username2, useremail2
AC, username3, useremail3
AD, username4, useremail4

Just to be clear, if I send @ID int = 1 and @IDName = ColumnID1

the @tempval will return AA
and the @sql will return username = username1 and useremail = useremail1
because of the @tempval - AA match

Hope this is clearer.



"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CC3E529EBC4DYazorman(a)127.0.0.1...
> Dan Guzman (guzmanda(a)nospam-online.sbcglobal.net) writes:
>> ALTER proc [dbo].[GetUserDetails]
>> @ID int,
>> @IDName varchar(100)
>> AS
>> BEGIN
>>
>> DECLARE
>> @sql_statement nvarchar(MAX),
>> @tempval varchar(100);
>>
>> SET @sql_statement = N'SELECT ' + @IDName + N' FROM dbo.tbl_main where
>> id
>> = @ID';
>>
>> EXEC sp_executesql
>> @sql_statement,
>> N'@ID int, @tempval varchar(100) OUTPUT',
>> @ID = @ID,
>> @tempval = @tempval OUTPUT;
>
> But @tmpval does not appear anywhere in the query?
>
> It is not clear to me what "MS Forum Newgroup User" want to achieve. I
> just posted an answer to an older question in .newusers where there was
> no indication of wanting to use dynamic SQL.
>
> This was the query I suggested in that group:
>
> Select l.username AS UName, isnull(l.useremail,'NA') AS UEmail
> from tbl_login l
> join tbl_main m ON m.column_name = l.designation
> where tbl_login.designation = @ID
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
 |  Next  |  Last
Pages: 1 2
Prev: generate summary row question
Next: SQL 2008 intellisense errors Invalid Object