How to regenerate a stored proc (drop & create or alter procedure) ??

Hi all.
Cf my other news: How to change or alter a user defined data type?
I need to regenerate (not only "sp_recompile") a list of stored procedures.
With the "SQL Server Enterprise Manager" it's very easy: just open each
Stored proc and save it. But how to do that by transact SQL script '
Thanks.
Lilian.What purpose does "regenerating" these serve? There are 2 ways to do this
depending on what your after. One way is to use the DROP PROCEDURE xxx
command and then issue the CREATE PROCEDURE xxx ... command. The other is
to use the ALTER PROCEDURE xxx.... command. Drop will not keep any
existing permissions and ALTER will.
--
Andrew J. Kelly
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u7Os%23CHQDHA.2228@.tk2msftngp13.phx.gbl...
> Hi all.
> Cf my other news: How to change or alter a user defined data type?
> I need to regenerate (not only "sp_recompile") a list of stored
procedures.
> With the "SQL Server Enterprise Manager" it's very easy: just open each
> Stored proc and save it. But how to do that by transact SQL script '
> Thanks.
>
> Lilian.
>sql

How to refer to Custom function in a query

I want to be able to refer to a custom function in a query
The Function is defined in the report proprties "Code" tab as follows:
public shared function MyCustomer as String
Return "ALFKI"
end function
I have tried different query formats, and I currently have:
="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
"'"
I am using the Northwind database as an example
Though the code compiles, I am unable to refer to any of the fields in
"customers"
I know there are other ways to accomplish this. What I am interested in
is how to refer to user defined code in QueriesYou might be able to pull a field list first with simply
SELECT * FROM customers
and then change the SQL to include the code. You might want to try the
latter using View Code and see of that gets around the auto-population of
fields step.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"cab0san" <cabosan@.gmail.com> wrote in message
news:1109783555.987331.113460@.g14g2000cwa.googlegroups.com...
>I want to be able to refer to a custom function in a query
> The Function is defined in the report proprties "Code" tab as follows:
> public shared function MyCustomer as String
> Return "ALFKI"
> end function
> I have tried different query formats, and I currently have:
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> I am using the Northwind database as an example
> Though the code compiles, I am unable to refer to any of the fields in
> "customers"
> I know there are other ways to accomplish this. What I am interested in
> is how to refer to user defined code in Queries
>|||Thanks, that works. A bit "klugy" but it works!
So, I set my query to
select * from customers, then execute the query, then click another
tab, causing the field list to populate, then switch back to the data
tab and change it to
="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
"'"
Behind the scenes that is accomplishing the step of adding the dataset
definition to my RDL|||I'm glad it's working.
I found that editing directly in the data tab works fine until you have a
complex SQL statement that uses its own declared variables (which for SQL
Server look similar to parameters: @.ParmName). When you add parameters
manually to the dataset, sometimes the data tab editing blows away those
manually entered parameters. That's when I use Bruce's suggestion to edit
SQL using View Code.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"cab0san" <cabosan@.gmail.com> wrote in message
news:1109862516.895060.241340@.l41g2000cwc.googlegroups.com...
> Thanks, that works. A bit "klugy" but it works!
> So, I set my query to
> select * from customers, then execute the query, then click another
> tab, causing the field list to populate, then switch back to the data
> tab and change it to
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> Behind the scenes that is accomplishing the step of adding the dataset
> definition to my RDL
>|||I have a similar issue however I am using a DB2 stored procedure so I dont
have the option to manipulate the sql. I was trying to put a translation on
the parameters list somehow and that doesnt seem to work although in the
Hitchhikers Guide it states that you can use and expression anywhere you have
the opportunity presented by <expression> or Fx ... which I see as an option
on the datasource parameters tab. I need to translate the client I receive
in as a parameter into a different value.
On the dataset parameters tab I have Parameter Name PM_Client_Code and the
value is =Code.TranslateClient(Parameters!PM_CLIENT_CODE.Value)
The function TranslateClient is this:
Function TranslateClient(ByVal clientnum As String) As String
TranslateClient = "ABC"
End Function
It doesnt work. Although the code works in vb .net just fine.
"cab0san" wrote:
> Thanks, that works. A bit "klugy" but it works!
> So, I set my query to
> select * from customers, then execute the query, then click another
> tab, causing the field list to populate, then switch back to the data
> tab and change it to
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> Behind the scenes that is accomplishing the step of adding the dataset
> definition to my RDL
>|||That looks good to me and should work. I suggest creating a report just with
your parameter and a textbox and assign this expression to the textbox (so
you can see what happens). Make sure that your parameter is exactly the name
you have here, the parameter is case sensitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:0EF45D79-4C8A-4B99-9D5A-30610FFB1F9D@.microsoft.com...
> I have a similar issue however I am using a DB2 stored procedure so I dont
> have the option to manipulate the sql. I was trying to put a translation
on
> the parameters list somehow and that doesnt seem to work although in the
> Hitchhikers Guide it states that you can use and expression anywhere you
have
> the opportunity presented by <expression> or Fx ... which I see as an
option
> on the datasource parameters tab. I need to translate the client I
receive
> in as a parameter into a different value.
> On the dataset parameters tab I have Parameter Name PM_Client_Code and the
> value is =Code.TranslateClient(Parameters!PM_CLIENT_CODE.Value)
> The function TranslateClient is this:
> Function TranslateClient(ByVal clientnum As String) As String
> TranslateClient = "ABC"
> End Function
> It doesnt work. Although the code works in vb .net just fine.
> "cab0san" wrote:
> > Thanks, that works. A bit "klugy" but it works!
> >
> > So, I set my query to
> >
> > select * from customers, then execute the query, then click another
> > tab, causing the field list to populate, then switch back to the data
> > tab and change it to
> > ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> > "'"
> > Behind the scenes that is accomplishing the step of adding the dataset
> > definition to my RDL
> >
> >

How to recall column name defined in CASE function

Hello all,

I need to refine a query in which one of the search conditions would depend on the value evaluated from the CASE function in SELECT statement. This returned column is named as "SLA". Now, the problem is I don't know how to recall this column in the WHERE clause as to do refinement. When I code it like SLA = @.Term, SQL Server returned an error: Invalid column name 'SLA'

If anyone knows a solution, please kindly let me know.

Thank you!

Here is the sample code:

ALTER PROCEDURE [dbo].[sp_premium_register]
@.PolicyType AS VARCHAR(10),
@.ReportFrom AS DATETIME,
@.ReportTo AS DATETIME,
@.Business AS VARCHAR(1),
@.Term AS VARCHAR(1)

SELECT
ColumnA,
ColumnB,

SLA =
CASE
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1 THEN 'L'
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) <= 1 THEN 'S'
END

FROM DNIssue D
LEFT OUTER JOIN KILRIShare S
ON (D.PolicyNo = S.PolicyNo AND D.PolicyType = S.PolicyType AND D.Origin = S.Origin AND D.EndorsementNo = S.EndrNo AND D.PartyNo = S.RINo)
LEFT OUTER JOIN KILPolicy P
ON (D.PolicyNo = P.PolicyNo AND D.PolicyType = P.PolicyType AND D.Origin = P.Origin AND D.EndorsementNo = P.EndrNo)
LEFT OUTER JOIN v_report_KILDNFund F
ON (D.DebitNote = F.DebitNote)
LEFT OUTER JOIN PolicyProfile R
ON R.Origin = D.Origin AND R.PolicyType = D.PolicyType

WHERE
SLA = @.Term

Order by D.PolicyType, D.DebitNote, D.Origin, D.PolicyNo, D.EndorsementNo, D.EntryDateuse an outer query
SELECT ColumnA
, ColumnB
, SLA
FROM (
SELECT ColumnA
, ColumnB
, SLA =
CASE
WHEN DateDiff(day, P.EffectiveFrom, P.EffectiveTo) > 1
THEN 'L'
else 'S'
END
FROM DNIssue D
LEFT OUTER
JOIN KILRIShare S
ON S.PolicyNo = D.PolicyNo
AND S.PolicyType = D.PolicyType
AND S.Origin = D.Origin
AND S.EndrNo = D.EndorsementNo
AND S.RINo = D.PartyNo
LEFT OUTER
JOIN KILPolicy P
ON P.PolicyNo = D.PolicyNo
AND P.PolicyType = D.PolicyType
AND P.Origin = D.Origin
AND P.EndrNo = D.EndorsementNo
LEFT OUTER
JOIN v_report_KILDNFund F
ON F.DebitNote = D.DebitNote
LEFT OUTER
JOIN PolicyProfile R
ON R.Origin = D.Origin
AND R.PolicyType = D.PolicyType
ORDER
BY D.PolicyType
, D.DebitNote
, D.Origin
, D.PolicyNo
, D.EndorsementNo
, D.EntryDate
) as t
WHERE SLA = @.Term or bite the bullet and code the datediff into the WHERE condition|||you'll have to repeat the case statement in your where clause|||It works! Thank you so much!!!