|
Hi there,
Who can help me with this one.
I have a form with a Checkbox treeview. Now the user select one or more items.
I want execute a procedure which gets all the items selected in the treeview.
I tryed a procedure similar like the example
CREATE PROCEDURE dbo.GET_InstrumentReport @SELECTED varchar(2000) AS
SELECT *
FROM tblKBRLIJST
WHERE (tblKBRLIJST.ID = @SELECTED) AND (tblKBRLIJST.Valid = 1)
GO
The Parameter @SELECTED conains the value's like '44&67&89'
When Execute this only the first one is returned.
any solution ?
Thanks.
|
|
|
|
|
Your query string should look like:
SELECT *
FROM tblKBRLIJST
WHERE tblKBRLIJST.ID IN (44,67,89) AND tblKBRLIJST.Valid = 1
Your problem is how to form (44,67,89) from a parameter. The easy way would be to use sp_executesql
CREATE PROCEDURE dbo.GET_InstrumentReport
(
@Selected NVARCHAR(2000)
)
AS
DECLARE @sql NVARCHAR(2000)
SET @sql = N'SELECT * FROM tblKBRLIJST WHERE tblKBRLIJST.ID IN (' +
REPLACE(@Selected,'&',',') +
N') AND tblKBRLIJST.Valid = 1'
EXEC executesql @sql
You can't use the parameter directly because it mixes values with SQL syntax. SQL syntax will be ignored if it is used as a parameter. Therefore, you must build the entire string by hand and pass it as a string.
Note: You should add some protection for SQL injection in the above code.
|
|
|
|
|
|
Thanks for the quick response,
I consider both solutions.
The solution from Michael Potter is nice as long I do not pass a to big amount of
parameters (the variable is limited to a max of 8000 bytes)
but as long it is less (for me its meaning that I can max lookup approx 50 records)
then it works fine for me.
But.
The last one takes some more programming but there isn't a limit on the max
records to select.
Do you use a real table or a table what is only in memory ?
If you use a real table do you use a table for each user or running procedure ?
The currently App I am writing does not have sensitive data so I am not worry about the
SQL Attack injection
So I think that I use the first solution for now and work the second solution out
when I planned to make a updated release.
|
|
|
|
|
RDoes wrote:
Do you use a real table or a table what is only in memory ?
I use a temporary table. i.e. When you create it you prefix the table name with a # and SQL Server puts it in the tempdb. At the end of the stored procedure it automatically drops the table for you.
RDoes wrote:
If you use a real table do you use a table for each user or running procedure ?
All tables are real. But the table is very short lived. You can create a large table if you want (e.g. if you are going to pass the same values several times then there is no point in recreating a table each time)
RDoes wrote:
but as long it is less (for me its meaning that I can max lookup approx 50 records)
then it works fine for me.
If you need more than 50 records (assuming that is the max you can do with an 8000 char parameter can handle) then you probably want to populate the table outside the stored procedure. In which case you may with to have a permanent table and add an extra column to prevent clashes between various users running procedures with the table at the same time.
RDoes wrote:
The currently App I am writing does not have sensitive data so I am not worry about the
SQL Attack injection
If I told you that through a SQL Injection attack it is possible to attack more than just SQL Server would you reconsider? For example I could use a SQL Injection attack to format the disk, or break out in to other parts of the operating system. It is even possible to compromise machines remote to the SQL Server if they are all within a trusted network.
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Colin Angus Mackay wrote:
I use a temporary table. i.e. When you create it you prefix the table name with a # and SQL Server puts it in the tempdb. At the end of the stored procedure it automatically drops the table for you.
Hmm Thats nice it is good to now that 
I think I going to use a static table so that when the user is selecting a node in the tree view it can be directly added in the table (with a GUID for that session to keep the users separated.)
Colin Angus Mackay wrote:
If I told you that through a SQL Injection attack it is possible to attack more than just SQL Server would you reconsider? For example I could use a SQL Injection attack to format the disk, or break out in to other parts of the operating system. It is even possible to compromise machines remote to the SQL Server if they are all within a trusted network.
Good point, I don’t now it can be done. How is that generally working?
Do I have that problem only when I use the Exec() method or also with other functions or methods
like WHERE for example (WHERE ID = @ID).
|
|
|
|
|
|
The first solution is not a great one: you lose almost all of the benefits of using a stored procedure in the first place, most importantly speed. Stored procedures are efficient because they are precompiled and their query plans are cached. This doesn't work for exec().
Secondly, you'll find that as you increase the number of parameters in the "IN" clause, performance will drop through the floor. 8000 characters, where the id's presumably are on average 4 digits + a comma long means your upper limit is 1600 parameters. This will not be nice to execute at all.
using System.Beer;
|
|
|
|
|
I agree with you,
If you have read the last reply from me you will see that I going to implement
a seconde table to store the selections.
This will be done when I going to release the updated version (must release this app soon)
And then run the Procedure with a Inner join to that table.
Then I don't have the limited numbers of items and the procedure is like it must be without the
Exec() command.
At the moment GUID are selected and not a ID of 4 digits. I only done that for the example to keep it readable.
At the moment I limit this selection method to a max of 50 ID's
|
|
|
|
|
Stored procedures are efficient because you don't have to go back and forth across the wire with the data when the requirements get complicated. This is not the case with the problem listed here.
I question your premise about the speed differences between ad-hoc queries and compiled stored procs. I think you will find that with SQL 2000 (& 2005) this is much (much....) less of an issue then it used to be. The query optimizer and cache system is quite impressive.
The reason I use stored procs is because of the data encapsulation, verification and auditing that the procs allow. SQL 2005 will only increase this capability.
As for your statement about the "IN" clause being inefficient, you are very correct. I was making the assumption that there would be only a few options selected because a human was doing the selecting.
As for feeding the data to a tmp table via the client and then running the query, I would assume the network traffic time would be much larger then the "IN" clause method. Parsing the "IN" clause via SQL and building the table there might be quicker for large "IN" clauses.
|
|
|
|
|
"As for feeding the data to a tmp table via the client and then running the query, I would assume the network traffic time"
yeah, i cunningly skipped over that cos it seems to be a bit of a nightmare 
using System.Beer;
|
|
|
|
|
I'm looking for a C# precompiler that let's me embed SQL in my C# programs.
It should have about the same feature set as SQLJ has for Java, e.g.
- statical sql syntax verified at compile time
- support for bind variables
- single row fetches into local variables
Does anybody know of such a product?
Thanks for your feedback.
|
|
|
|
|
RoyceFHi,
I am trying to connect to my (local) server to get a list of databases on that server. It doesn't work when I pass (local) as the server name, but it works if I hardcode in the actual name. My connect string is:
Data Source=(local);Initial Catalog=master;User ID=sa;Password=;Integrated Security=SSPI;
Does anyone know how to get the actual server name?
TIA,
Royce
Strive for Perfection, Achieve Excellence.
|
|
|
|
|
rfickling@iac-online.com wrote:
I am trying to connect to my (local) server to get a list of databases on that server. It doesn't work when I pass (local) as the server name, but it works if I hardcode in the actual name.
Try localhost
The server name will be the same as the machine name, unless you have installed named instances (in which case localhost will probably need to be followed by a slash and the name of the instance)
Might I also suggest that using the sa account, especially with no password, is inviting an attacker to take out your SQL Server. Also, be aware that someone with that kind of access could potentially run something like
xp_cmdshell 'format c: /fs:ntfs /v:0wn3d' And wow! Just look at the amount of free space on the C drive, but hey! Where did my operating system go?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Thanks, Colin, but that doesn't work either. The resulting error is "SQL Server does not exist or access denied." Yes, I understand how servers get their names. However, the installer (usually our IT people) has a great deal of flexibility in this. We have at least one server named mach_name\mach_name - I don't know why. So to assume that the server will always be named the same as the machine name is asking for trouble. And on the use of the sa account, I am trying to convince people here that this is a risk that we should not take.
Royce
Strive for Perfection, Achieve Excellence.
|
|
|
|
|
select datasource from sysservers
this will give you the data source name.
|
|
|
|
|
Irsh,
What is the connect string for use with this select statement? My problem is connecting to a server that I don't have its name, not getting data from it. The generic server name (local) doesn't always work, so I need the actual server name in order to connect.
Thanks,
Royce
|
|
|
|
|
as for i know, give the name of u r sever like raghu or venu in data source clause
Eg:data source="raghu"
e.veera raghavendra
|
|
|
|
|
One other note... anything done with the SET cannot be part of a procedure. It will actually change the variable until the sql server is restarted or it is changed back using SET variable
Tojamismis
A mind never grows without mistakes and questions.
|
|
|
|
|
I'm trying to select records where a date occurs between today and the end of the current week with Sunday as the last day of the week. I'd do this with the following code:
SET DATEFIRST 1
SELECT * FROM sometable WHERE recordDate BETWEEN GETDATE() AND DATEADD(d, 7 - DATEPART(dw, GETDATE()), GETDATE()))
This works great, except that I need to make a View that returns this recordset. SET DATEFIRST 1 is not valid in CREATE VIEW.
I also tried making a User Defined Function that returns the date at the end of the week using the logic in the second part of the BETWEEN, but the SET DATEFIRST 1 is not valid in the function either.
Anyone know how I can get my intended result -- the date on sunday of the current week?
|
|
|
|
|
DATEFIRST is a server variable, so you can't execute SET on it in any type of procedure. You'll need to either change it separately or you could check with your DBA to see if there is any problem with making the change permanent.
Tojamismis
A mind never grows without mistakes and questions.
|
|
|
|
|
Hi
I have two Classes: Invoice and LineOfInvoice. In class LineOfInvoice I have a field named "line_number". My question is: in a datagrid the user removes line 3 of the invoice. What is the best way to update the line number field of invoice after third line (witch was removed). Line 4 becomes line 3, line 5 becomes line 4, ...
Remove all lines and insert all lines again with the right numbering from datagrid?
update only de rows after the line removed?
Thanks!
|
|
|
|
|
I did something similar to this a while ago.
I just updated the lines after the one that was removed with an SQL like this:
UPDATE [tableName]
SET line = line - 1
WHERE idKey = @idKey
AND line > @removedLine
Hope it helps!
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hello all
i try to insert string with this char ' but the sql condsider it with the sql command an give me an error
Inset into Table (name , email) values ('haytham's',dod@hotmial.com)
Thanks
|
|
|
|
|
use parametrized query, it will work plus it prevents you from SQL injection.
see MSDN[^] for details.
best regards,
David 'DNH' Nohejl
Never forget: "Stay kul and happy" (I.A.)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
