1

could someone explain why we need to declare a variable @work in the below code?

CREATE FUNCTION dbo.GetURL (@URL VARCHAR(250))
    RETURNS VARCHAR(250)
    AS BEGIN
        DECLARE @Work VARCHAR(250)

        SET @Work = @URL
        SET @Work = SUBSTRING(@work, CHARINDEX('.', @work) + 1, LEN(@work))   
        SET @Work = SUBSTRING(@work, 0, CHARINDEX('.', @work))
        RETURN @work
    END

Can we re-frame the code like below? if not, please explain it in a more layman's terms.

CREATE FUNCTION dbo.GetURL (@URL VARCHAR(250))
    RETURNS VARCHAR(250)
    AS BEGIN
        SET @URL = SUBSTRING(@URL, CHARINDEX('.', @URL) + 1, LEN(@URL))   
        SET @URL = SUBSTRING(@URL, 0, CHARINDEX('.', @URL))
        RETURN @URL
    END
Improve this question
3
  • 5
    Did you try it? Commented Apr 6, 2017 at 7:54
  • The both are the same. Commented Apr 6, 2017 at 7:55
  • Basically its the same thing.. Commented Apr 6, 2017 at 7:57

3 Answers 3

Reset to default
1

Your function is just simply getting the second (.) of your url given like

thisis.my.url

it will return a result of my

As per your question

Can we re-frame the code like below?

My Answer is Yes

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Both the code snippets are equivalent in functionality. The only difference is that the first code snippet is creating a copy of the original value of @URL and then modifying the copy variable @Work in place of modifying the original parameter itself. This way you have the original value of the parameter available to your function till the end in case you need it later for comparison or any other work.

As long as you are not going to use the original value of the parameter again in your function there is no point of creating a copy of it.

I believe your refactoring effort is in correct direction. You should use second code snippet as the new definition of your function.

Improve this answer

Comments

-1

You declared a variable @Work with a capital W. But in this part

SET @Work = SUBSTRING(@work, CHARINDEX('.', @work) + 1, LEN(@work))

you use the @work variable with a lower case w. Just change it to a capital W and it will work.

Improve this answer

4 Comments

sql is case-INsensitive, so this is not a problem.
@HansKesting SQL-Server is definitely case sensitive. I just tried out and SQL-Server will ask you to declare the variable if it's lower case when you declare it and upper case when you use it.
Not my experience: when you try to declare @test and @TEST, both Sql Azure and Sql Express will complain that the variable has already been declared.
And your answer doesn't answer the question "why do we need that extra variable anyway, when we can re-use the parameter?"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.