Calling a proc that is passed a SQLstring

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calling a proc that is passed a SQLstring

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-07 : 18:23:14

Hi,

I have to call the below utility proc in my procedure. I am unable to get the result I want. Can someone help tell me what I need to do in my main proc to call the utility proc and get the string of product names separated by comma stored in a column name?

example @Product is '100' and the ShortName should be stored in a column in the final result set in my proc as

'USHG,FRN,EM,HY,AGNCY,AGNCYF,CXHG,ITHG,CXEM,EMLM,HYC'

You pass two parameters to the called proc: A string that represents a query for the data column you want to retrieve, and the name of the column.

Requirements: Returns a comma seperated string of a single column from a select statement

Parameters: @SQLstring = The complete SELECT statement used to get the column rows
@ColumnName = The name of the column to use (this would also be in @SQLstring)
Example:
--Get CSV list of Products
declare @list varchar(8000), @Query varchar(4000)
SET @Query = 'SELECT ShortName
FROM Product
WHERE ProductID IN(SELECT CONVERT(int, PARAM_VALUE)
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = ' + @Product + ')'
EXEC misUtilityReturnCSV
@Query, 'ShortName', @ProductSelect OUTPUT

print @list

ALTER PROCEDURE [dbo].[misUtilityReturnCSV]
@SQLstring nvarchar(4000),
@ColumnName varchar(50)

AS
BEGIN
DECLARE @newSQL nvarchar(4000)

SET @newSQL = 'SELECT DISTINCT CONVERT(varchar(50),' + @ColumnName + ') ' +
RIGHT(@SQLstring,(LEN(@SQLstring)-CHARINDEX ('FROM' , @SQLstring))+2)

CREATE TABLE #workingtable
(
i int IDENTITY (1, 1) NOT NULL ,
vc varchar (100),
)

insert into #workingtable (vc) EXECUTE sp_executesql @newSQL

DECLARE @ColumnValues varchar(8000)
SELECT @ColumnValues = ISNULL(@ColumnValues + ',', '') + vc
FROM #workingtable
SELECT @ColumnValues
END

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-07 : 19:26:03

The output of the proc called is

@SQLstring ColumnName
SELECT ShortName from Product WHERE ProductID IN(SELECT CONVERT(int, PARAM_VALUE) FROM MARPTGEN_PARAMLIST WHERE PARAMLIST_KEY = 100) NULL

Should I just declare another variable in my proc called @DisplayProduct? I would like the result of the select statement in @SQLstring be stored in a column.

How do I achieve this? Any help is appreciated. Thanks

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-07 : 21:59:33

Hi,

I do not believe that the utility proc works correctly. It is never used or called by any other proc.

I am trying to find another way to be able to retrieve the names of multiple products based on the @Product passed to the stored proc.

DECLARE @STRstring varchar(4000)
SET @STRstring = 'SELECT ShortName
FROM Product
WHERE ProductID IN(SELECT CONVERT(int, PARAM_VALUE)
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = ' + @Product + ')'

Can I declare @STRstring as OUTPUT? How can I help get the result set of the select stored in @STRstring?

For example if @Product = 100 then the result set of @STRstring will be as

ShortName

USHG
FRN
EM
HY
AGNCY
AGNCYF
CXHG
ITHG
CXEM
EMLM
HYC
I want the above result set as a CSV as below. Thanks for your help.

'USHG,FRN,EM,HY,AGNCY,AGNCYF,CXHG,ITHG,CXEM,EMLM,HYC'

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-07 : 23:41:20

The other method I thought about is to create a temp table with an IDENTITY column and I got it to work

CREATE TABLE #ProductName
(
i int IDENTITY (1, 1) NOT NULL ,
ProductName varchar(4000)

)

INSERT #ProductName
(
ProductName
)

SELECT ShortName
FROM Product
WHERE ProductID IN(SELECT CONVERT(int, PARAM_VALUE)
FROM MARPTGEN_PARAMLIST
WHERE PARAMLIST_KEY = ' + @Product + ')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources