June 22, 2016 at 12:30 pm
Hello, I have a small If statement that I would like to ask for help to convert it to SQL syntax.
If I am correct, the If statement checks for a period on a string passed, and then if found it removes it from the string.
If InStr(PID, ".") > 0 Then
PID = Left(PID, InStr(PID, ".") - 1)
End If
Thank you for your help.
June 22, 2016 at 12:45 pm
Basically, you just change InStr for CHARINDEX(). The following code simplifies the validation, the FROM is just used to generate sample data.
SELECT LEFT(PID, CHARINDEX('.', PID + '.') - 1)
FROM (VALUES('123.21'), ('132456'))x(PID)
June 22, 2016 at 12:53 pm
Thank you for your solution Luis.
I have a question. I need to replace the VB code with SQL and put it in a stored procedure that will take in a VARCHAR parameter named PID.
How can I adjust your proposed solution to be dynamic in a stored procedure?
Thank you.
June 22, 2016 at 1:06 pm
I tried to do the following down below, but it is not working since it returns nothing:
/*
EXEC Test_String 'VQAL1-SQ994.1'
*/
Stored Procedure contents:
ALTER PROCEDURE [dbo].[Test_String] (@ProductID VARCHAR(25))
AS
BEGIN
DECLARE @ID VARCHAR (25)
SET @ID = (
SELECT
LEFT(@ProductID, LEN(@ProductID) - LEN(REPLACE(@ProductID, '.', '')) - 1)
);
SELECT @ID;
END;
June 22, 2016 at 1:24 pm
It's not returning anything because you're telling to return nothing. Your string has a length of 13 chars, if you remove the period, it will only have 12 chars. You're selecting LEFT( @ProductID, 13-12-1).
Why do you want a variable? Why didn't you use the code I provided? You over complicated it. Also, this shouldn't be in a stored procedure.
CREATE PROCEDURE [dbo].[Test_String] (@ProductID VARCHAR(25))
AS
SELECT LEFT(@ProductID, CHARINDEX('.', @ProductID + '.') - 1);
GO
EXEC Test_String 'VQAL1-SQ994.1';
GO
DROP PROCEDURE Test_String;
June 22, 2016 at 3:00 pm
Thank you Luis C.
June 23, 2016 at 8:39 am
itortu (6/22/2016)
Thank you Luis C.
You're welcome.
Do you understand that this is not the best use of a stored procedure? Stored procedures (as any database code) is meant to work with data sets and not with scalar values.
June 23, 2016 at 12:57 pm
Luis Cazares (6/23/2016)
itortu (6/22/2016)
Thank you Luis C.You're welcome.
Do you understand that this is not the best use of a stored procedure? Stored procedures (as any database code) is meant to work with data sets and not with scalar values.
So if I'm writing a windows program to update individual rows you're saying we have to grant table updates to the user instead of using stored procedures? Would Oracle work better for screen apps in this regard?
June 23, 2016 at 1:52 pm
patrickmcginnis59 10839 (6/23/2016)
Luis Cazares (6/23/2016)
itortu (6/22/2016)
Thank you Luis C.You're welcome.
Do you understand that this is not the best use of a stored procedure? Stored procedures (as any database code) is meant to work with data sets and not with scalar values.
So if I'm writing a windows program to update individual rows you're saying we have to grant table updates to the user instead of using stored procedures? Would Oracle work better for screen apps in this regard?
A single row is not a scalar value. A single column in a single row is still a data set.
If you're going to update a single column in a single row from a table, you still need to have permissions over the table. Changing a variable's value is not the function of a stored procedure (it might be of a function).
June 23, 2016 at 3:03 pm
Luis Cazares (6/23/2016)
patrickmcginnis59 10839 (6/23/2016)
Luis Cazares (6/23/2016)
itortu (6/22/2016)
Thank you Luis C.You're welcome.
Do you understand that this is not the best use of a stored procedure? Stored procedures (as any database code) is meant to work with data sets and not with scalar values.
So if I'm writing a windows program to update individual rows you're saying we have to grant table updates to the user instead of using stored procedures? Would Oracle work better for screen apps in this regard?
A single row is not a scalar value. A single column in a single row is still a data set.
I think that since he's on a database server, he's probably working with data, otherwise why not do everything in vb? Since he's doing a select on the variable, seems like it would then allow access to the updated variable via the result set, although obviously I'm sure we're not seeing his entire requirements or for that matter the entire stored procedure, like I said, he's probably moving to a database server procedure for a reason. Of course, and just as obviously, I'm making assumptions LOL
If you're going to update a single column in a single row from a table, you still need to have permissions over the table.
Why wouldn't just granting execute on the stored procedure to the updating user work? Seems to work that way on my dev server, albeit 2008. I can test on 2012 also, but I wouldn't expect that drastic of a change.
https://msdn.microsoft.com/en-us/library/bb669058(v=vs.110).aspx
One method of creating multiple lines of defense around your database is to implement all data access using stored procedures or user-defined functions. You revoke or deny all permissions to underlying objects, such as tables, and grant EXECUTE permissions on stored procedures. This effectively creates a security perimeter around your data and database objects.
Seems like it makes sense to me.
Changing a variable's value is not the function of a stored procedure (it might be of a function).
I update variables in stored procedures all the time, hence my curiosity on why its wrong 🙂 Obviously, the stored procedure is on a database because I'm doing database stuff, otherwise like the OP's original program, I could do it without a database.
I do understand that he could use a function, but if its not a set oriented operation, I'm wondering if there is SOMETHING ELSE implied here that gives a function the advantage.
But I do apologize for the questions if its off topic or junking up the thread, let me know and I can delete my text/questions.
June 24, 2016 at 6:33 am
Your comments are completely right. I just don't want to assume something with someone new to SQL.
It's not a problem if we change variables' values in a stored procedure, the problem comes when it's the only thing done. That's what I was trying to make clear and I might not have expressed myself correctly.
June 24, 2016 at 8:37 am
Hello Luis, I did understand your point and I also want to add that the use of a variable was just at the beginning of the stored procedure, and that it also was the only part in which a scalar variable was being used. With that said, I also want to mention that I ended up leaving that part of the logic flow outside of the stored procedure, in the front end app.
Once again, thank you for your answer and explanation.
June 24, 2016 at 12:40 pm
I would like to point out that the expression
If InStr(PID, ".") > 0 Then
PID = Left(PID, InStr(PID, ".") - 1)
End If
will truncate the string starting with the first "." that it finds. It does not "remove the period" as the OP had stated.
June 24, 2016 at 1:13 pm
This is correct. Thank you William.
June 24, 2016 at 1:19 pm
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply