SEO

I am trying to figure out the solution for this stored procedure
Basically here is what i have in the stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[GetColumnName]
-- Add the parameters for the stored procedure here
@sTableName varchar(50),@sObjectClass varchar(50),@sFieldNumber
varchar(50), @sColumnName varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
set @sColumnName = ('select column_name FROM '+ @sTableName +
'.Information_Schema.columns '+'where Table_Name = '+''''+@sObjectClass
+''''+ ' and Column_Name like '+''''+@sFieldNumber+'-%''')
END

I call the Stored Procedure as
Declare @sColumnName varchar(50)
exec dbo.GetColumnName 'TestData6061','accounting','11',@sColumnName
OUTPUT
select @sColumnName
go

If I run
SELECT column_name
FROM TestData6061.Information_Schema.columns
where Table_Name = 'accounting' and Column_Name like '11-%' which is
what the stored procedure is doing, I get '11-AccountSpecifictype'.
What am I missing in the stored procedure that returns the value?
Please help. Thanks in advance

Vic (vikrantp@gmail.com) writes:
> CREATE PROCEDURE [dbo].[GetColumnName]
> -- Add the parameters for the stored procedure here
> @sTableName varchar(50),@sObjectClass varchar(50),@sFieldNumber
> varchar(50), @sColumnName varchar(50) OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> set @sColumnName = ('select column_name FROM '+ @sTableName +
> '.Information_Schema.columns '+'where Table_Name = '+''''+@sObjectClass
> +''''+ ' and Column_Name like '+''''+@sFieldNumber+'-%''')
> END
>
> I call the Stored Procedure as
> Declare @sColumnName varchar(50)
> exec dbo.GetColumnName 'TestData6061','accounting','11',@sColumnName
> OUTPUT
> select @sColumnName
> go
>
> If I run
> SELECT column_name
> FROM TestData6061.Information_Schema.columns
> where Table_Name = 'accounting' and Column_Name like '11-%' which is
> what the stored procedure is doing, I get '11-AccountSpecifictype'.

Which is what the procedure is doing? Eh? The procedure assigns the variable
string expression, which will be truncated after 50 characters, as
that is how long you have declared the parameter. That string starts
with 'select column_name FROM ', and there is no way it can become
'11-AccountSpecifictype'.

It appears that you have invented your own syntax, but that is not going
to take you anywhere.

Maybe this is what you mean:

DECLARE @sql nvarchar(MAX),
@params nvarchar(MAX)
SELECT @sql = select @columnname = COLUMN_NAME FROM '+ @db +
'.INFORMATION_SCHEMA.COLUMS ' +
'WHERE TABLE_NAME = @objectclass ' +
' AND COLUMN_NAME LIKE @fieldnumber',
@params = ' @columnname sysname, @objectclass sysname, ' +
' @fieldnumber sysname'
EXEC sp_executesql @sql, @params, @columnname OUTPUT,
@objectclass, @fieldnumber


If you have not read my article on dynamic SQL, I recommend that you do,
because you seem to need it. At least the parts that describe sp_executesql.
http://www.sommarskog.se/dynamic_sql.html

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mar 1, 3:39 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Vic (vikra...@gmail.com) writes:
> > CREATE PROCEDURE [dbo].[GetColumnName]
> > -- Add the parameters for the stored procedure here
> > @sTableName varchar(50),@sObjectClass varchar(50),@sFieldNumber
> > varchar(50), @sColumnName varchar(50) OUTPUT
> > AS
> > BEGIN
> > SET NOCOUNT ON;
> > set @sColumnName = ('select column_name FROM '+ @sTableName +
> > '.Information_Schema.columns '+'where Table_Name = '+''''+@sObjectClass
> > +''''+ ' and Column_Name like '+''''+@sFieldNumber+'-%''')
> > END
>
> > I call the Stored Procedure as
> > Declare @sColumnName varchar(50)
> > exec dbo.GetColumnName 'TestData6061','accounting','11',@sColumnName
> > OUTPUT
> > select @sColumnName
> > go
>
> > If I run
> > SELECT column_name
> > FROM TestData6061.Information_Schema.columns
> > where Table_Name = 'accounting' and Column_Name like '11-%' which is
> > what the stored procedure is doing, I get '11-AccountSpecifictype'.
>
> Which is what the procedure is doing? Eh? The procedure assigns the variable
> string expression, which will be truncated after 50 characters, as
> that is how long you have declared the parameter. That string starts
> with 'select column_name FROM ', and there is no way it can become
> '11-AccountSpecifictype'.
>
> It appears that you have invented your own syntax, but that is not going
> to take you anywhere.
>
> Maybe this is what you mean:
>
> DECLARE @sql nvarchar(MAX),
> @params nvarchar(MAX)
> SELECT @sql = select @columnname = COLUMN_NAME FROM '+ @db +
> '.INFORMATION_SCHEMA.COLUMS ' +
> 'WHERE TABLE_NAME = @objectclass ' +
> ' AND COLUMN_NAME LIKE @fieldnumber',
> @params = ' @columnname sysname, @objectclass sysname, ' +
> ' @fieldnumber sysname'
> EXEC sp_executesql @sql, @params, @columnname OUTPUT,
> @objectclass, @fieldnumber
>
> If you have not read my article on dynamic SQL, I recommend that you do,
> because you seem to need it. At least the parts that describe sp_executesql.http://www.sommarskog.se/dynamic_sql.html
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi Erland, thanks for the reply I was checking out your sp_executesql
related page and it was pretty helpful. So I modified my code to
something like this but its giving me some problem during execution.
Also the print @sql isn't showing values of @db. So I tried to modify
the select statement by modifying the the single quotes (') in the
line then I see the error as @db not declared or something. Also Exec
sp_executesql @sql, @params, 'TestData', 'accounting', '11' is that
how I call sp_executesql?

DECLARE @sql nvarchar(MAX),@params nvarchar(MAX),@valueofrow
varchar(50)

SELECT @sql =
N' SELECT COLUMN_NAME '+
N' FROM @db.INFORMATION_SCHEMA.COLUMNS '+
N' WHERE TABLE_NAME = @objectclass AND '+
N' COLUMN_NAME LIKE @fieldnumber-% '

print @sql

SELECT @params =
N' @db sysname, '+
N' @objectclass sysname, '+
N' @fieldnumber sysname, '+
N' @columnname varchar(50) OUTPUT '

exec sp_executesql @sql,
@params,'TestData6061','accounting','11',@columnam e = @valueofrow
OUTPUT

Vic (vikrantp@gmail.com) writes:
> Also the print @sql isn't showing values of @db. So I tried to modify
> the select statement by modifying the the single quotes (') in the
> line then I see the error as @db not declared or something.

You have:

> N' FROM @db.INFORMATION_SCHEMA.COLUMNS '+

I don't think this can ever be legal T-SQL. In any case it can never mean
what you intended. You cannot specify the database name through a
variable, but you have to inline it:

N' FROM '*+ quotename(@db) + '.INFORMATION_SCHEMA.COLUMNS '+

> Also Exec sp_executesql @sql, @params, 'TestData', 'accounting', '11'
> is that how I call sp_executesql?

The first parameter is the SQL statement. The second is the parameter
list. The remaining are the parameters as specified in your parameter
list.

> N' COLUMN_NAME LIKE @fieldnumber-% '

This is not going to end happily. - is a numeric operator, but % will not
convert to an integer. You need:

> N' COLUMN_NAME LIKE @fieldnumber + ''-%'' '




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mar 2, 6:03 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Vic (vikra...@gmail.com) writes:
> > Also the print @sql isn't showing values of @db. So I tried to modify
> > the select statement by modifying the the single quotes (') in the
> > line then I see the error as @db not declared or something.
>
> You have:
>
> > N' FROM @db.INFORMATION_SCHEMA.COLUMNS '+
>
> I don't think this can ever be legal T-SQL. In any case it can never mean
> what you intended. You cannot specify the database name through a
> variable, but you have to inline it:
>
> N' FROM ' + quotename(@db) + '.INFORMATION_SCHEMA.COLUMNS '+
>
> > Also Exec sp_executesql @sql, @params, 'TestData', 'accounting', '11'
> > is that how I call sp_executesql?
>
> The first parameter is the SQL statement. The second is the parameter
> list. The remaining are the parameters as specified in your parameter
> list.
>
> > N' COLUMN_NAME LIKE @fieldnumber-% '
>
> This is not going to end happily. - is a numeric operator, but % will not
> convert to an integer. You need:
>
> > N' COLUMN_NAME LIKE @fieldnumber + ''-%'' '
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for the reply. So I modified my select line as below (as per
your instruction)
SELECT @sql =
N' SELECT COLUMN_NAME '+
N' FROM'+ quotename(@db)+'.INFORMATION_SCHEMA.COLUMNS '+
N' WHERE TABLE_NAME = @objectclass AND '+
N' COLUMN_NAME LIKE @fieldnumber+"-%" '

n now I a call sp_executesql as
exec sp_executesql @sql,
@params,@db='TestData6061',@objectclass='accountin g',@fieldnumber='11',@columname
= @valueofrow OUTPUT
Is that how am I supposed to call sp_executesql? I get an error for
'must declare the scalar variable @db' in the select line when I run

Vic (vikrantp@gmail.com) writes:
> Thanks for the reply. So I modified my select line as below (as per
> your instruction)
> SELECT @sql =
> N' SELECT COLUMN_NAME '+
> N' FROM'+ quotename(@db)+'.INFORMATION_SCHEMA.COLUMNS '+
> N' WHERE TABLE_NAME = @objectclass AND '+
> N' COLUMN_NAME LIKE @fieldnumber+"-%" '

The last line should be

N' COLUMN_NAME LIKE @fieldnumber+''-%'' '

Double single quotes, not single double quotes. (I strongly recommend
that you set your newsreader to show posts with a fixed font.)

> n now I a call sp_executesql as
> exec sp_executesql @sql,
> @params,@db='TestData6061',@objectclass='accountin g',@fieldnumber='11',@columname
>= @valueofrow OUTPUT
> Is that how am I supposed to call sp_executesql? I get an error for
> 'must declare the scalar variable @db' in the select line when I run

So why just declare it then?

But @db is not a parameter to the SQL command, only a parameter to
the surrounding procedure, so it should not appear in the call to
sp_executesql.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mar 2, 9:16 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Vic (vikra...@gmail.com) writes:
> > Thanks for the reply. So I modified my select line as below (as per
> > your instruction)
> > SELECT @sql =
> > N' SELECT COLUMN_NAME '+
> > N' FROM'+ quotename(@db)+'.INFORMATION_SCHEMA.COLUMNS '+
> > N' WHERE TABLE_NAME = @objectclass AND '+
> > N' COLUMN_NAME LIKE @fieldnumber+"-%" '
>
> The last line should be
>
> N' COLUMN_NAME LIKE @fieldnumber+''-%'' '
>
> Double single quotes, not single double quotes. (I strongly recommend
> that you set your newsreader to show posts with a fixed font.)
>
> > n now I a call sp_executesql as
> > exec sp_executesql @sql,
> > @params,@db='TestData6061',@objectclass='accountin g',@fieldnumber='11',@columname
> >= @valueofrow OUTPUT
> > Is that how am I supposed to call sp_executesql? I get an error for
> > 'must declare the scalar variable @db' in the select line when I run
>
> So why just declare it then?
>
> But @db is not a parameter to the SQL command, only a parameter to
> the surrounding procedure, so it should not appear in the call to
> sp_executesql.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I finally got it working. Thanks a million. Btw as you mentioned 'I
strongly recommend that you set your newsreader to show posts with a
fixed font'. I just read my group mails by loggin onto google groups.
I'll take a look to change the settings. Thanks

Vic (vikrantp@gmail.com) writes:
> I finally got it working. Thanks a million. Btw as you mentioned 'I
> strongly recommend that you set your newsreader to show posts with a
> fixed font'. I just read my group mails by loggin onto google groups.
> I'll take a look to change the settings. Thanks

I seem to recall that Google has a setting to show posts in fixed fonts.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Mar 3, 2:22 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Vic (vikra...@gmail.com) writes:
> > I finally got it working. Thanks a million. Btw as you mentioned 'I
> > strongly recommend that you set your newsreader to show posts with a
> > fixed font'. I just read my group mails by loggin onto google groups.
> > I'll take a look to change the settings. Thanks
>
> I seem to recall that Google has a setting to show posts in fixed fonts.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

i see. I'll check it out. Thanks. I have one more question regarding
the same issue. Is there anyway I can store the output of the above
mentioned sp_executesql into a sql variable ? or can I create a
temporary table #temp and do insert #temp
sp_executesql @sql, @params,@objectclass,@fieldnumber,@columnname
OUTPUT and do select * on it to return the value?