using araylist in stored proc

Question

using araylist in stored proc

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

natigsqlserver SSC-Addicted Points: 488 More actions · Answer 1

Thom A - Saturday, December 30, 2017 11:53 AM
You can define table parameters in C#. I'd suggest keeping with the Custom Table Data Type and incorporating it into your application, rather than a delimited string.
Have a research yourself on how to achieve this first.

with cursor can we give this query
insert into table name select col1,col2

for example this query give me thats result

select c.cols,v.vals from #col c left join #val v on c.id=v.id

cols    vals
ad    natig
phone    90
test    NULL

fro example from that cursor

declare @column nvarchar(max),@value nvarchar(max),@sql nvarchar(max),@tablename nvarchar(max)='test'
declare hesab cursor for
select c.cols,v.vals from #col c left join #val v on c.id=v.id
open hesab
fetch next from hesab into @column,@value
while @@FETCH_STATUS=0
begin

//////some query hier
end

this result

insert into test (ad,phone,test) select 'natig',90
??/
thank you

Thom A SSC Guru Points: 99133 More actions · Answer 2

No, don't use a cursor.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

natigsqlserver SSC-Addicted Points: 488 More actions · Answer 3

Thom A - Saturday, December 30, 2017 12:55 PM
No, don't use a cursor.

oh ok
thank you

Thom A SSC Guru Points: 99133 More actions · Answer 4

Have a look at Table-Valued Parameters, specifically the section titled "Configuring a SqlParameter Example". It explains everything you need on how to use those SP's I provided from C#. As I said before, you should know how to use a simple Search Engine do do your own research as well: C# use a T-SQL custom table type parameter

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99133 More actions · Answer 5

Just noticed an error in my UPDATE script. New version below (and full script attached again):

CREATE PROC crud_update_operation @TableName varchar(100), @ID int, @ColVals crud_columns_and_values READONLY
AS
  DECLARE @SQL nvarchar(4000), @parm nvarchar(100);
  --Start off with the basics again
  SELECT @SQL = N'
  UPDATE ' + QUOTENAME([name]) + NCHAR(10)--Quote the table name, incase it has special characters
  FROM sys.tables --We use sys.tables, to ensure the table exist. If it doesn't exist, then NULL the returned (and no SQL is run)
  WHERE [name] = @TableName;
  --Again, I'm not 100% happy on this, but I *think* we've avoided injection here. I am more than happy for someone to prove me wrong here though
  SET @SQL = @SQL + N'SET ' + STUFF((SELECT N',' + QUOTENAME(c.name) + N' = ''' + REPLACE(CV.ColumnValue,N'''',N'''''') + N''''
             FROM sys.columns c
               JOIN sys.tables t ON c.object_id = t.object_id
               JOIN @ColVals CV ON c.[name] = CV.ColumnName
             WHERE t.[name] = @TableName
             ORDER BY c.[name] --ORDERING IS IMPORTANT!
             FOR XML PATH(N'')),1,1,N'') + NCHAR(10);
  SET @SQL = @SQL + N'WHERE ID = @dID;';             
  SET @parm = N'@dID int';
  PRINT @SQL;
  EXEC sp_executesql @SQL, @parm, @dID = @ID;
GO

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Now my question, if Thom gets hit by a bus, can you support the code he wrote? Can you explain to others not familiar with the code how it works and why? If you answer no to these questions, don't use it. You have to realize that YOU are the one that has to support the code not some anonymous person on the Internet.

And again, WHY do you need to do it this way? You NEVER answered that question.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Thom, you may want to use NVARCHAR(MAX) for the dynamic SQL. Only allowing 2000 characters may not be enough depending on the table names, column names, and data.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Also, may want to add error trapping just in case table names and/or column names are missing or misspelled.

Thom A SSC Guru Points: 99133 More actions · Answer 9

Lynn Pettis - Saturday, December 30, 2017 2:52 PM
Thom, you may want to use NVARCHAR(MAX) for the dynamic SQL. Only allowing 2000 characters may not be enough depending on the table names, column names, and data.

4000*

Yes, you could easily replace the 4000 with a MAX though. It's more force of habit as I have a lot of stuff at work that contains delimited string, and make (good) use of Jeff's splitter; thus using a MAX makes the performance suffer.

Lynn Pettis - Saturday, December 30, 2017 2:54 PM
Also, may want to add error trapping just in case table names and/or column names are missing or misspelled.

I took me long enough to get what I did done! Lol. I was hopeful (heh) that the OP might be happier to do some error trapping; or after starting to implement it, ask how to do so. Like I said, it is a Saturday. 😉

Lynn Pettis - Saturday, December 30, 2017 2:46 PM
Now my question, if Thom gets hit by a bus, can you support the code he wrote? Can you explain to others not familiar with the code how it works and why? If you answer no to these questions, don't use it. You have to realize that YOU are the one that has to support the code not some anonymous person on the Internet.
And again, WHY do you need to do it this way? You NEVER answered that question.

And yes, that is the killer question, WHY.

After spending all this time, however, I'm a little concerned by the private messages the OP has been sending me.

natigsqlserver, please don't send me PM's like that; and I am most certainly not "your dear". If you want to discuss the SQL I've written you, do so on this thread. I'm actually seriously considering that those messages are being sent by some kind of Bot...

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Thom A - Saturday, December 30, 2017 3:07 PM

Lynn Pettis - Saturday, December 30, 2017 2:52 PM
Thom, you may want to use NVARCHAR(MAX) for the dynamic SQL. Only allowing 2000 characters may not be enough depending on the table names, column names, and data.

4000*

Yes, you could easily replace the 4000 with a MAX though. It's more force of habit as I have a lot of stuff at work that contains delimited string, and make (good) use of Jeff's splitter; thus using a MAX makes the performance suffer.

Lynn Pettis - Saturday, December 30, 2017 2:54 PM
Also, may want to add error trapping just in case table names and/or column names are missing or misspelled.

I took me long enough to get what I did done! Lol. I was hopeful (heh) that the OP might be happier to do some error trapping; or after starting to implement it, ask how to do so. Like I said, it is a Saturday. 😉

Lynn Pettis - Saturday, December 30, 2017 2:46 PM
Now my question, if Thom gets hit by a bus, can you support the code he wrote? Can you explain to others not familiar with the code how it works and why? If you answer no to these questions, don't use it. You have to realize that YOU are the one that has to support the code not some anonymous person on the Internet.
And again, WHY do you need to do it this way? You NEVER answered that question.

And yes, that is the killer question, WHY.

After spending all this time, however, I'm a little concerned by the private messages the OP has been sending me:

how are your my dear
i want learn your script
was liked me

and

have you facebook
i want send private message in face
hier not goo
my profile is [removed]
http://facebook.com/%5Bremoved%5D%5B/quote%5D
natigsqlserver, please don't send me PM's like that; and I am most certainly not "your dear". If you want to discuss the SQL I've written you, do so on this thread. I'm actually seriously considering that those messages are being sent by some kind of Bot...

You are correct, 4000, not 2000. It is Saturday and I have yet to get a good nights sleep all week. As soon as I lay down, I can't breath I get so congested. I have even taken some medication for it but it doesn't last. Need to find something else I guess.

I write a lot of dynamic SQL and I have never needed to use the delimited split routines on the code I wrote. Used then in the code at times but that didn't stop me from using nvarchar(max) for the dynamic SQL itself.

Thom A SSC Guru Points: 99133 More actions · Answer 11

Lynn Pettis - Saturday, December 30, 2017 3:14 PM
You are correct, 4000, not 2000. It is Saturday and I have yet to get a good nights sleep all week. As soon as I lay down, I can't breath I get so congested. I have even taken some medication for it but it doesn't last. Need to find something else I guess.
I write a lot of dynamic SQL and I have never needed to use the delimited split routines on the code I wrote. Used then in the code at times but that didn't stop me from using nvarchar(max) for the dynamic SQL itself.

Hope you feel better soon then Lynn. 🙂

Maybe that Fireball you're so fond of (it is you, right..?) might clear you up with tomorrow night's festivities. 😉

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Thom A - Saturday, December 30, 2017 3:19 PM
Lynn Pettis - Saturday, December 30, 2017 3:14 PM
You are correct, 4000, not 2000. It is Saturday and I have yet to get a good nights sleep all week. As soon as I lay down, I can't breath I get so congested. I have even taken some medication for it but it doesn't last. Need to find something else I guess.
I write a lot of dynamic SQL and I have never needed to use the delimited split routines on the code I wrote. Used then in the code at times but that didn't stop me from using nvarchar(max) for the dynamic SQL itself.
Hope you feel better soon then Lynn. 🙂
Maybe that Fireball you're so fond of (it is you, right..?) might clear you up with tomorrow night's festivities. 😉

I should try that tonight. I bought a second bottle and some rum chata for Christmas, but we never opened them. I blame my youngest daughter for hooking me on Fireball. Still prefer my Crown Royal.

natigsqlserver SSC-Addicted Points: 488 More actions · Answer 13

Thom A - Saturday, December 30, 2017 3:07 PM

Lynn Pettis - Saturday, December 30, 2017 2:52 PM

natigsqlserver, please don't send me PM's like that; and I am most certainly not "your dear". If you want to discuss the SQL I've written you, do so on this thread. I'm actually seriously considering that those messages are being sent by some kind of Bot...

what about you Tom?
its simple private message
pls clear my message from forum

natigsqlserver SSC-Addicted Points: 488 More actions · Answer 14

Thom A - Saturday, December 30, 2017 3:19 PM

and you sql script don't will help my problem
what about you ?
i want to another format calling query
exec dbo.procname @tablename='xxx',@column='xxxx',@values='xxxxx'

your proc working you you think

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

natigsqlserver - Sunday, December 31, 2017 3:08 AM
Thom A - Saturday, December 30, 2017 3:19 PM
and you sql script don't will help my problem
what about you ?
i want to another format calling query
exec dbo.procname @tablename='xxx',@column='xxxx',@values='xxxxx'
your proc working you you think

Is it really that hard to answer a simple question? Why are you looking for a single procedure to do all things?