January 24, 2007 at 3:02 pm
I need to solve the following problem in a SP.
If I have the following data which can be 1..many records
1 FieldValue1
2 FieldValue2
3 FieldValue3
4 FieldValue4
5 FeildValue5
.
.
.
I need these to be outputed in a SP as columns added to the end of a bunch of other data. So the column headings would be something like.
PersonID, PersonLastName, PersonFirstName, FieldValue1, FieldValue2, FieldValue2, ...
That being said, this need to be dynamic.
Hope this makes sense
January 25, 2007 at 8:13 am
If I read this correctly, you will actually need two queries - the first to pull the supplemental field names, and the second to pull legitimate data. I don't know of a clean way to do this, but it can be done.
If this is to be a reusable solution, I would recommend creating a scalar-valued UDF that would return a properly formatted string (something like ", NULL AS [FieldValue1], NULL [AS FieldValue2] ...") according to the query you use to pull the data.
Then you can dynamically build your query in your sproc and append the output from your UDF to the select list:
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT Value1, Value2, Value3' + mydb.dbo.MyNewUDF()
+ ' FROM myTable WHERE [some condition]'
EXEC @sql
If you go this route, I would recommend doing some tests on the data within your UDF to make sure you don't get unexpected values in your SQL. Dynamic SQL is powerful, but proper checking must be done to make sure you don't leave yourself vulnerable to an attack (or silly mistakes).
Again, this would not be an elegant solution but it should get the job done.
hth
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
January 25, 2007 at 4:35 pm
Tim, Thanks this is what I was looking for and this solved the problem but presents another one. The Select statement is more than 4000 characters long. Does NVARCHAR(MAX) cut off at 4000 characters in a SP? What is the MAX size of the @sql variable in your example?
January 25, 2007 at 5:10 pm
max indicates that the maximum storage size is 2^31-1 bytes
http://msdn2.microsoft.com/en-us/library/ms186939.aspx
MohammedU
Microsoft SQL Server MVP
January 26, 2007 at 9:35 am
Mohammed, I did read this yesterday however, when I tried to exec @sql with more than 4000 charachters it errored at the 4000 character or cut of at that point. Maybe it's a error with the exec. I doubt that though. Trying to figure out why it is erroring where it is.
January 26, 2007 at 12:30 pm
It is working fine for my test.. see the following sample...
CREATE
TABLE BigStrings
(
BigString
VARCHAR(MAX)
)
INSERT
BigStrings (BigString)
VALUES
(REPLICATE('1', 8000))
select
DATALENGTH(BigString) from BigStrings
INSERT
BigStrings (BigString)
VALUES
(REPLICATE(CONVERT(VARCHAR(MAX), '1'), 100000))
select
DATALENGTH(BigString) from BigStrings
declare
@VarMax Varchar(Max)
select
@VarMax = (REPLICATE(CONVERT(VARCHAR(MAX), '1'), 100000))
INSERT
BigStrings (BigString)
select
@VarMax
select
DATALENGTH(BigString) from BigStrings
MohammedU
Microsoft SQL Server MVP
February 1, 2007 at 7:33 pm
EXEC @sql will try execute a named stored procedure where the name is stored in @sql.
EXEC (@SQL) will execute the SQL stored in the variable @sql
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply