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