CODEWITHSUNDEEP
c#sqlt-sqlparametersprocedure
gdubs
gdubs

Reputation: 2754

multiple parameters for stored procedure

I need to select 45 fields from a record which has 96 of them (dont ask me, but i can't normalize them, i would if i could). So, I have this page who would need all those 45 on them once it's loaded in front of the user.

Basically, I was thinking, I would make a new stored proc that would retrieve all the fieldnames and put them into one field and all the values and put them into another field and basically would end up with two parameters. I would then end up processing them in C#.

Now my question is, 1, is it the right way to do it? 2nd, if it is I can't figure out how to select the fields and put it on one parameter.

select @sql = ' select 'field1' + 'field2' + 'field3'.....

im confused on where to start?

Upvotes: 0

Views: 3911

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can return the data as xml in one field.

Test table

create table TestTbl(ID int, F1 int, F2 int, F3 int, F4 int) -- to F96

Test data

insert into TestTbl values (1, 2, 3, 4, 5)

Query

select
  (select
    F1, F2, F3, F4 -- to F45 
   from TestTbl
   where ID = 1
   for xml path('root'), type) as XMLData

Result

XMLData
-----------------------------------------------------
<root><F1>2</F1><F2>3</F2><F3>4</F3><F4>5</F4></root>

XML in XMLData field

<root>
  <F1>2</F1>
  <F2>3</F2>
  <F3>4</F3>
  <F4>5</F4>
</root>

Upvotes: 0

ChevCast
ChevCast

Reputation: 59234

Well for one thing you are making this way more complex than it needs to be. How in the world you have 96 columns on one table I will never know, but to select the 45 you need you're just going to have to type out 45 columns in the select statement.

Here is a sample of what the SQL would look like. Naturally I'm not going to type 45 columns, but you get the idea:

SELECT FirstName, LastName, Age, [keep going to 45] FROM tblUsers

The other issue I would like to address is the way you are executing your SQL statement. NEVER EVER EVER EVER EVER EVER EVER concatenate string variables into one SQL string. Make sure you are using parameterized queries at the very least. But I would recommend looking into Entity Framework or LINQ to SQL sometime as well.

SqlCommand scomm = new SqlCommand("UPDATE tblUsers SET FirstName='" + firstName + "' WHERE UserID='" + userId + "'");

That ^^^ equals very bad. Think about what would happen if a user decided to be sneaky and make his first name Harry' AND Admin='true. You might think, "Oh, well I'll just do firstName = firstName.Replace("'","''"); on all my variables. If you do that I will personally come punch you. Parameterize your queries like this:

SqlCommand scomm = new SqlCommand("UPDATE tblUsers SET FirstName=@FirstName WHERE UserID=@UserID");
scomm.Parameters.Add(new SqlParameter("FirstName", firstName));
scomm.Parameters.Add(new SqlParameter("UserID", userId));

That ^^^ equals much better.

EDIT Also if you ever get a chance to re-work that monster of a table you have, try refactoring subsets of fields into their own entity (table) and linking them via a reference ID. For example, say I have a table called [tlbUsers] and it contains info about a specific user. Like this:

[tlbUsers]
UserID
FirstName
LastName
Age
Username
StreetAddress
City
State
ZipCode
Country
Phone

Consider refactoring that so that related values have their own table. You could take all the address info from this users table and put it in a table called tlbAddresses. Not only would that make it easier to deal with when pulling in the data, but it could potentially save you space in the database. For instance, if Harry and Sally both live in the same home, they could reference the same address record.

[tlbUsers]
FirstName
LastName
Age
Username
AddressID
Phone

[tlbAddresses]
AddressID
Street
City
State
ZipCode
Country

Upvotes: 2

Aaron Silverman
Aaron Silverman

Reputation: 22655

I am having a little trouble understanding your question, however if you want to pass a variable number of parameters to a stored procedure there are two ways I can think of that you can do it, which require SQL Server 2005 and SQL Server 2008 respectively.

The first leverages XML. Have your procedure take a varchar(max) argument and then you can easily split it out. For example, if you comma separate what you want, you could:

DECLARE @xml xml
SET @xml = cast('<x>'+replace(@yourArg,',','</x><x>')+'</x>' as xml)

SELECT N.value('.','varchar(max)') AS myArgName FROM @xml.nodes('x') AS T(N)

Also, you could leverage table valued variables and select your inputs into a table and pass that to the stored procedure. See http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters for an example.

Upvotes: 0

Related Questions