Stored procedure in Query

[M8KWR]

Hopefully this is simple question, which i am struggling with.

I have a stored procedure (lets call is sp_1), this will only ever return 1 value.

I want to use this within a normal query, so for example.

Select Column1, Sp_1(Column2) as New_Column2
From Table1

Is this possible?

Many thanks

 

[Crowley16]

no, you cannot call a proc in the middle of a select statement.

What you want is a udf...

--------------------
Procrastinate Now!

 

[M8KWR]

Many thanks, but either i am loosing the plot or just don't get it.

I am used to access, creating a module, then using this within the query (easy)

From what i have read i need an Inline Table Function, but this doesn't seem to want to work...

What i have is a table (drop_downs) that holds descriptions, within the normal table (table1), the columns hold the guid values, which relate to the drop_downs table.

I have got this functions

Function [dbo].[dd_GUID_Description] (@GUID_Value varchar(36))

RETURNS TABLE
AS
RETURN
SELECT dbo.Drop_Downs.Description FROM drop_downs

WHERE DropDown_Guid =@GUID_Value


But how do i create a query to use this more then once, so in table1, i have many columns having a GUID value.

Many thanks in advance, as this is driving me crazy today!

 

[gmmastros]

There are two types of functions. Table-valued and Scalar functions. A table valued function can return a table of data (multiple rows and multiple columns). A scalar function can only return a single value. If you make a scalar function, you can call it from within the select clause. For example...

Create Function [dbo].[dd_GUID_Description] (@GUID_Value varchar(36))
RETURNS [!]VarChar(100)[/!]
AS
Begin 
RETURN (
        SELECT dbo.Drop_Downs.Description  
        FROM drop_downs
        WHERE DropDown_Guid =@GUID_Value
        )
End

Note that I used varchar(100) as the return type. This should match the data type of the description column. Since this is a scalar function, you can use it in the select clause like this:

Select Column1, dbo.dd_GUID_Description(Column2) as New_Column2
From Table1

There are a couple other comments I would like to make about this. First, there is a data type specifically for guids called UniqueIdentifier. When you store a GUID as a string, it takes 36 bytes when you include the dashes or 32 bytes if you remove them. Given the nature of GUID's (each character can be 0 through F), you can actually store the data in 16 bytes by compressing/combining the data. SQL Server does this automatically when you use the UniqueIdentifier data type. Storage space is cheap, so size doesn't matter too much. However, when you join/filter using GUIDs, it's better to use a 16 byte value instead of a 36 byte string. With millions of rows, you will notice a performance improvement by using UniqueIdentifier.

My next comment also involves performance. When you use a function, there is an inherent overhead associated with it. Each call to a function is pretty fast, but depending on how you use the function, it can cause real performance problems for you. My (personal) criteria for a function is two-fold. It must satisfy both conditions before I bother creating a function. 1. The function must be sufficiently complicated that I want to hide the implementation details. 2. The function must be re-usable by several queries. If the block of code does not satisfy both criteria, I will not make it a function.

From the sounds of it, you don't really need a function here. A simple join to the table should suffice. It also sounds like you may want the same join on several columns from the same master table. In that case, you should be able to accomplish this using table & column aliases. Like this...

Select Table1.Column1,
       Alias1.Description As Alias1Description,
       Alias2.Description As Alias2Description
From   Table1
       Inner Join Drop_Downs As Alias1
         On Table1.Column2 = Alias1.DropDown_Guid
       Inner Join Drop_Downs As Alias2
         On Table1.Column3 = Alias2.DropDown_Guid

Multiple joins will be many times faster than calling a function for each row.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[M8KWR]

Excellent..

I could have many joins, but the tables are every changing, and without have to re-create the joins, and making sure they are correct, i though a different tactic was called for, and this is perfect, many thanks.