Using Defined Constants In Stored Procedures

Apr 21, 2008

I would like to create a file named macro_def.m that contains the following:

define(_HELP,1000)
define(_INSERT,2000)
define(_UPDATE,3000)
define(_DELETE,4000)

define(_retcode,`eval($1)')

Then in any stored procedure that I have I would like to do something like:

CREATE PROCEDURE [dbp].[sp_AddNew]
INCLUDE "macro_def.m"
BEGIN
insert into tablex (name,number) values ("text",_retcode(_INSERT))
END
GO

Questions:
What is the syntax in the SP to inlude a file like macro_def.m ?
Where do I save the macro_def.m file ?
Do I need to include a path in the INCLUDE statement ?

View 5 Replies


ADVERTISEMENT

How Do I Use Constants In Stored Procedures?

Jul 20, 2005

I have several instances of "magic number" variables (tinyints). In myprogram, I have assigned an enumeration to make the meaning clear, such as:enum Condition {Green = 0,Yellow,Red}In my database, one of the tables contains a "Condition" field (tinyint),which stores the number 0, 1 or 2. However, in my Stored Procedures I amhaving to use magic numbers as follows:SELECT * From Nodes Where Condition = 1(to select all nodes with yellow condition)Obviously, meaning is obfuscated here. I would rather use constants but nothave to re-define them in every stored procedure I use.I there any way to do this?

View 1 Replies View Related

User Defined Functions To Stored Procedures

Jun 1, 2004

Hello all:

Running into a brain problem here. I remeber reading an article a while back (2002?) on either Visual Studio Magazine or MSDN Magazine where there was a way to generate Stored Procedures from User Defined Functions. I need this information in order to do my job as it is also a way to cut down on time for this project I am trying to finish. Does anyone have the code or remeber what I am talking about. I just finished Kathleen Dollards article again on using XSLT to generate code but would really like to use the User Defined Functions.

I searched for the article on line but came up dry. Searched through all my magazines but could not find the article. Any help would be greatly appreciated. Bit of topic I guess but still relevant to the board.

Thanks

View 1 Replies View Related

Stored Procedures And User Defined Functions

Feb 4, 2006

What are the pros and cons of each?

One advantage that I can see withh UDFs is that they are a bit a Views with parameters. You can perform joins on UDF columns (which you cannot do with a Stored Proc). You can do the same with Views but UDFs have the advantage that you restrict the number of rows with a parameterised WHERE (or HAVING) clause.

View 4 Replies View Related

Diff Between User Defined Functions And Stored Procedures

Jun 19, 2008

Hi to all,            Can any body tell me what is the difference between Stored procedures and User Defined Functions ?             In my assumption Function return a value or table, but SP doesn't return value instead of that SP use select statement or assign value to output statement. This is right?           

View 3 Replies View Related

User Defined Data Types And Stored Procedures

Mar 13, 2001

I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message

Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0
Column or parameter #1: Cannot find data type udtcol1.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #2: Cannot find data type udtcol2.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #3: Cannot find data type udtcol3

Can you have user defined data types in stored procedures.

Store Procedure creation text

CREATE PROCEDURE spStoredproc
@col1 udtcol1,
@col2 udtcol2,
@col3 udtcol3
AS
INSERT INTO tblTempEmployee
(col1 , col2 , Col3)
VALUES (@col1 , @col2, @col3)
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO

Dave

View 3 Replies View Related

User-defined Stored Procedures InsertCustomerin Northwind Database That Is Cached In Database Explorer:No New Values Inserted?

Jan 14, 2008

Hi all,

I put "Northwind" Database in the Database Explorer of my VB 2005 Express and I have created the following stored procedure in the Database Exploror:

--User-defined stored procedure 'InsertCustomer'--

ALTER PROCEDURE dbo.InsertCustomer

(

@CustomerID nchar(5),

@CompanyName nvarchar(40),

@ContactName nvarchar(30),

@ContactTitle nvarchar(30),

@Address nvarchar(60),

@City nvarchar(15),

@Region nvarchar(15),

@PostalCode nvarchar(10),

@Country nvarchar(15),

@Phone nvarchar(24),

@Fax nvarchar(24)

)

AS

INSERT INTO Customers

(

CustomerID,

CompanyName,

ContactName,

ContactTitle,

Address,

City,

Region,

PostalCode,

Country,

Phone,

Fax

)

VALUES

(

@CustomerID,

@CompanyName,

@ContactName,

@ContactTitle,

@Address,

@City,

@Region,

@PostalCode,

@Country,

@Phone,

@Fax

)
=================================================
In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code:
--Form_Kimmel.vb--
Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form_Kimmel


Public Sub InsertCustomer()

Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _

"Initial Catalog=northwind;Data Source=NAB-WK-EN12345"

Dim connection As SqlConnection = New SqlConnection(connectionString)

connection.Open()

Try

Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)

command.CommandType = CommandType.StoredProcedure

command.Parameters.Add("@CustomerID", "PAULK")

command.Parameters.Add("@CompanyName", "Pauly's Bar")

command.Parameters.Add("@ContactName", "Paul Kimmel")

command.Parameters.Add("@ContactTitle", "The Fat Man")

command.Parameters.Add("@Address", "31025 La Jolla")

command.Parameters.Add("@City", "Inglewoog")

command.Parameters.Add("@Region", "CA")

command.Parameters.Add("@Counrty", "USA")

command.Parameters.Add("@PostalCode", "90425")

command.Parameters.Add("@Phone", "(415) 555-1234")

command.Parameters.Add("@Fax", "(415 555-1235")

Console.WriteLine("Row inserted: " + _

command.ExecuteNonQuery().ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

Throw

Finally

connection.Close()

End Try



End Sub

End Class
==============================================


I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.

Thanks in advance,
Scott Chang

View 10 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Why User Defined Procedure Shows Under System Procedures In Object Explorer

Jan 17, 2008

I have created a store procedure in sql server 2005.
At first, it shows up as a normal user defined store procedure in Object explorer.

Recently, I found I could not found the procedure in normal place. But the execution is fine.
Finally I found it stays under system procedure folder.


How this could happen?

View 8 Replies View Related

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

Using Constants In DTS

Mar 22, 2004

I am really new to DTS but I have had moderate success creating packages to move and tranform data in one DB to a new DB. The problem I am having is that I can not figure out how to use a constant in my transformation.

I am moving data from an old DB to a new DB. Currently all of my users in the old DB will have the same user role in the new DB. So I basically want to move all of my user ids to a lookup table and then give them all the same role id. Seems like it should be easy, but no luck here.

Does anyone have some advice?

thanks

View 1 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

String Constants Must End With A Double Quote.

Apr 2, 2007

i got this error when i was trying to deploy the report... in the preview mode report is working good....



D:
eport1.rdl The expression for the query €˜dataset€™ contains an error: [BC30648] String constants must end with a double quote.



can any one gimme the solution




View 11 Replies View Related

Passing Constants In Data Flows

Jan 13, 2006

Hi

Does anyone know what would be the best technique to use for passing constants into data flows shapes?

For example if I had a lookup that required a static value to be passed into it as part of a concatenated key etc...

Cheers

Al

View 1 Replies View Related

String Constants Must End With A Double Quote.

May 5, 2008

Hi There,

I can't figure out what is causing this error: can you please have a look at my code and let em know what is wrong or rework it for me:

="SELECT ARIBC.cntbtch, ARIBC.btchdesc, ARIBC.AUDTUSER, ARIBC.AUDTDATE, ARIBC.AUDTTI, GLPSOFTMAP.PSPRODUCT, GLPSOFTMAP.PSDEPT, GLPSOFTMAP.PSACCOUNT, GLPSOFTMAP.ACCTID, ARIBH.CODECURN, ARIBH.INVCDESC, CONVERT(varchar,ARIBH.FISCYR + ARIBH.FISCPER) as PERIOD, CONVERT(varchar,ARIBH.IDCUST) AS ACCOUNT, ARCUS.NAMECUST as ARNAME, CASE TEXTTRX WHEN 1 THEN 'INV' WHEN 2 THEN 'DM' WHEN 3 THEN 'CM' END AS CLASS, CONVERT(varchar,ARIBH.IDINVC) AS TRX_NUMBER, (CASE TEXTTRX WHEN 3 THEN ROUND((ARIBD.AMTEXTN*-1),2) ELSE ROUND((ARIBD.AMTEXTN),2) END) AS ""AMOUNT FROM ARIBC"" INNER JOIN ARIBH ON ARIBC.CNTBTCH = ARIBH.CNTBTCH INNER JOIN ARCUS ON ARIBH.IDCUST = ARCUS.IDCUST INNER JOIN ARIBD ON ARIBH.CNTBTCH = ARIBD.CNTBTCH AND ARIBH.CNTITEM = ARIBD.CNTITEM INNER JOIN GLAMF ON ARIBD.IDACCTREV = GLAMF.ACCTFMTTD INNER JOIN GLPSOFTMAP ON ARIBD.IDACCTREV = GLPSOFTMAP.ACCTID WHERE ARIBC.BTCHSTTS=3 AND ERRENTRY <= 0 AND BTCHDESC NOT LIKE '%54-8003%' AND ARIBH.IDINVC in ('" &Replace(Parameters!invoicenum.Value,",","','")"

Thanks,
RC

View 1 Replies View Related

The Name 'HeaderID' Is Not Permitted In This Context. Only Constants, Expressions, Or

Oct 5, 2007

I'm pulling data from one database in which the headerID is the primary key which automatically increases by one on each insert. Modifying all of the other fields from one data type to another and then inserting them into another database after the convertion is done. I keep getting this error.

The name 'HeaderID' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

INSERT INTO [DatabaseName].[dbo].[InvoiceHeaderConverted]
([HeaderID], [InvoiceNumber], [StoreNumber], [DeliveryDate], [DueDate],
[TotalInvoice], [TotalTax], [ManifestNumber])
VALUES(HeaderID = @HeaderID, InvoiceNumber = @InvoiceNumber, StoreNumber = @StoreNumber,
DeliveryDate = @DeliveryDate, DueDate = @DueDate, TotalInvoice = @TotalInvoice,
TotalTax = @TotalTax, ManifestNumber = @ManifestNumber)

Can anyone point me in the right direction on this one. The headerid isn't autogenerated in the second database but is still the primary key.

View 4 Replies View Related

Stored Proc Vs User-defined Function

Aug 9, 2005

Dear all,After reading the Book Online, I am still confued by when to use store proc or user-defined function. The most obvious different for me is that 1. UDF can return a table    eg select * from dbo.UDF(a, b , c) In real word application, what factor should i consider?Also, any debug tools in sql server ?Ad_dee

View 3 Replies View Related

User-Defined-Function With-in Stored-Procedure??

Apr 14, 2008

Does MS-SQL allow us to create an user-defined function within the stored-procedure script? I have been getting errors. It's my first time using the user-defined function with stored-procedure. I welcome your help.


Code:


CREATE FUNCTION ftnVehicleYearFormattor (@sValue VARCHAR(2))
RETURNS VARCHAR(2)
AS
BEGIN
IF (LEN(@sValue) < 2)
SET @sValue = '0' + @sValue

RETURN @sValue
END



Thanks...

View 4 Replies View Related

Stored Procedure Vs User Defined Functions

Apr 4, 2008

Hi All,

My question is :

Why we are using udf inside stored procedures ?
Will it make any performance faster for the stored procedure to execute ?

awaiting your reply.

Thanks
Renjith

View 6 Replies View Related

Stored Procedure And User-Defined Functions

Sep 25, 2006

lokesh writes "1) What are the differences between "Stored Procedure" and "User-Defined Functions"?

2) Places where we use/don't use Stored Procedure/User-Defined Functions."

View 2 Replies View Related

Stored Procedure - User Defined Function.

Jul 20, 2005

Hi.I'm really new to MSSQL, so therefore my question can sound stupid.Is it possible to use a function written in a module in MS-ACCESS in astored procedure?Or how can it be done, it is a complicated function with loop and more.I'll appreciate all answers also negatives ones.TIAJørn

View 1 Replies View Related

How To Use User Defined Function In Stored Procedure?

Mar 14, 2006

Hello friends,

I want to use my user defined function in a stored procedure.

I have used it like ,

select statement where id = dbo.getid(1,1,'abc')

//dbo.getid is a user defined function.



procedure is created successfully but when i run it by exec procedurename parameter



I get error that says

"Cannot find either column "dbo" or the user-defined function or aggregate "dbo.getid", or the name is ambiguous."


Can any body help me?



Rgds,

Kiran.

View 3 Replies View Related

Error : Only Constants, Constant Expressions, Or Variables Allowed Here.

Jun 9, 2004

hi all,
when I try to do the following insert for the table test

create table test (outputs character(10), chk integer)

insert into test values('a',((select count(*) from test where outputs='a')+1))

I am getting the error
The name 'outputs' is illegal in this context. Only constants, constant expressions, or variables allowed here. Column names are illegal.

when i tried the same in DB2 it's working fine. is there anyerror in my syntax or this kind of function not allowed in SQL Server.

regards
Melb

View 1 Replies View Related

Stored Procedure And Calling User Defined Function

Sep 29, 2007

I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)

Short list of process flow:

1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER

2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient

3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient

4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page

5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.

<FLOW WITH CODE AND FUNCTIONS :>

This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.

This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.

This is code in Form.asp

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

If ValidateInput(Request.Querystring("Postback"))="FormDetails" then 'Check
Postback Type

'We need to load up vendors associated with the current client.

'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------

'If the call returns 1, then the employee has access.

'Otherwise, just write out "Access to this client is denied."

'CALL SP - Not sure what parameters need to go with it or its syntax

Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)

'When it returns can check it here........

if ab_HasAccessToClient result is a 1 then

'boolean would be 1 so show panel

Else

'boolean would be 0 so show access denied

'allow them to go back to the original page.

end if

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

ON SQL SERVER: Stored Procedure

----------------------------------------------------------
--------------------------------

rx_sp_HasAccessToClient

CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]

@EmployeeID INT,

@ClientSSN varchar(50),

@ReturnBitValue = OUTPUT

/*

' Parameters here passed via call from Form.asp - not sure what is passed
yet.

*/

AS

set nocount on

/*

Written by Mike Belcher 9/27/2007 for Form.asp

'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.

'Gets return bit and passes that back to the call from Form.asp

*/

GO

----------------------------------------------------------
--------------------------------

ON SQL SERVER: User-Defined Function

----------------------------------------------------------
--------------------------------

ab_HasAccessToClient

CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))

@ClientSSN varchar(50),

@EmployeeID,

@ReturnBitValue = OUTPUT

AS

SELECT 1

FROM tblEmployeesClients ec

INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN

INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName

WHERE e.EmployeeID= @EmployeeID

AND c.InActiveClient=0

AND c.ClientSSN = @ClientSSN

'Some Code here to save result bit ..

RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient

----------------------------------------------------------
--------------------------------

</FLOW WITH CODE AND FUNCTIONS :>

View 5 Replies View Related

Stored Procedure And Calling User Defined Function

Sep 29, 2007

I seem to be getting tasks that I am not familiar with these days. I am a
guy that has coded it all in the asp page or in the code behind in .NET.
This problem is outlined below and I need a help / advice on doing this. I
had the flow of the 3 parts to it expanded below. A call is made to a Stored
Procedure, The SP then calls a user defined function that runs SQL, this
returns a 1 or 0 to the SP which then returns the value back to the call on
the asp page. This is a lot I know but it is the way the lead guy wants it
done. Any help so I can keep most of the hair I have left is appreciated :-)

Short list of process flow:

1. Form.asp calls to rx_sp_HasAccessToClient in SQL SERVER

2. rx_sp_HasAccessToClient then calls ab_HasAccessToClient

3. ab_HasAccessToClient runs SQL command on db and sends return bit back to
rx_sp_HasAccessToClient

4. rx_sp_HasAccessToClient then sends this back to the call in the Form.asp
page

5. Form.asp then checks the Boolean and if 1 then show or if 0 then deny.

<FLOW WITH CODE AND FUNCTIONS :>

This is not the correct syntax but is showing what I understand sort of how
this is to be done so far.

This panel loads up the Vendors and id's when the user clicks on the link
"view detailed list of vendors associated with this client". This is the
beginning of the process.

This is code in Form.asp

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

If ValidateInput(Request.Querystring("Postback"))="Fo rmDetails" then 'Check
Postback Type

'We need to load up vendors associated with the current client.

'--------- CHECK ACCESS HERE via function ab_HasAccessToClient
--------

'If the call returns 1, then the employee has access.

'Otherwise, just write out "Access to this client is denied."

'CALL SP - Not sure what parameters need to go with it or its syntax

Execute_SP("rx_sp_HasAccessToClient '" & ClientSSN & "', 1)

'When it returns can check it here........

if ab_HasAccessToClient result is a 1 then

'boolean would be 1 so show panel

Else

'boolean would be 0 so show access denied

'allow them to go back to the original page.

end if

'PANEL ONE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXX----
>

ON SQL SERVER: Stored Procedure

----------------------------------------------------------
--------------------------------

rx_sp_HasAccessToClient

CREATE PROCEDURE [dbo].[ rx_sp_HasAccessToClient]

@EmployeeID INT,

@ClientSSN varchar(50),

@ReturnBitValue = OUTPUT

/*

' Parameters here passed via call from Form.asp - not sure what is passed
yet.

*/

AS

set nocount on

/*

Written by Mike Belcher 9/27/2007 for Form.asp

'Calls ab_HasAccessToClient function - not sure of the syntax as of yet,
just making flow.

'Gets return bit and passes that back to the call from Form.asp

*/

GO

----------------------------------------------------------
--------------------------------

ON SQL SERVER: User-Defined Function

----------------------------------------------------------
--------------------------------

ab_HasAccessToClient

CREATE FUNCTION ab_HasAccessToClient (@employeeID INT, @ClientSSN
VARCHAR(50))

@ClientSSN varchar(50),

@EmployeeID,

@ReturnBitValue = OUTPUT

AS

SELECT 1

FROM tblEmployeesClients ec

INNER JOIN tblClients c ON ec.ClientID = c.ClientSSN

INNER JOIN tblEmployees e ON ec.Employee = e.EmployeeLogInName

WHERE e.EmployeeID= @EmployeeID

AND c.InActiveClient=0

AND c.ClientSSN = @ClientSSN

'Some Code here to save result bit ..

RETURN @ReturnBitValue 'Back to rx_sp_HasAccessToClient

----------------------------------------------------------
--------------------------------

</FLOW WITH CODE AND FUNCTIONS :>

View 1 Replies View Related

Calling User Defined Function From Stored Procedure

Jul 30, 2007

When i call user defined function from stored procedure, i am getting an error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.MyFunction'.


why this error is happening?.

i am calling this way... dbo.MyFunction(param1,param2)

View 6 Replies View Related

User Defined Function To Stored Procedure Call?

Apr 7, 2008

Hello,

Can we call stored procedure from user defined function and vice-versa??

Thanks in advance.

View 4 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Strange Problew With User Defined Function Or Stored Procedure

Jul 23, 2005

I am trying to add a simple case statement to a stored procedure oruser defined function. However when I try and save thefunction/procedure I get 2 syntax errors. Running the query in queryanalyser works fine and a result is given with no syntax errors. Ibelieve its something to do with the spaces in the field names. Not mychoice as its an existing system I have to work around. Any helpgreatly appreciatedSQL QueryDECLARE @pfid VARCHAR(100)SET @pfid = '000101'SELECTCaseWHEN GetDate()BETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-("dbo"."Navision_Cost_Prices"."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfidUser Defined Function (Errors Line 11 & 15)CREATE FUNCTION dbo.get_Margin(@pfid VARCHAR(100), @dtNow DATETIME)RETURNS DECIMAL ASBEGINDECLARE @Return as DECIMALSET @Return = (SELECTCaseWHEN @dtNowBETWEEN gg_shop_product.sale_start AND gg_shop_product.sale_endTHEN((((gg_shop_product.Sale_Price/100)/1.175)-(dbo.Navision_Cost_Prices."Unit Cost" *Navision_Codes."Navision QTY"))/((gg_shop_product.Sale_Price/100)/1.175)) * 100WHEN dbo.Navision_Cost_Prices."Unit Cost" = 0Then '100'WHEN gg_shop_product.list_price > 0 THEN((((gg_shop_product.List_Price /100)/1.175)-("dbo"."Navision_Cost_Prices"."UnitCost"*dbo.Navision_Codes."NavisionQTY"))/((gg_shop_product.List_Price/100)/ 1.175)) * 100END as 'Margin'from gg_shop_product INNER JOINgg_shop_variant ON gg_shop_product.pf_id =gg_shop_variant.pf_id LEFT OUTER JOINgg_shop_cost_prices ON gg_shop_product.pf_id =gg_shop_cost_prices.pf_id INNER JOINNavision_Codes ON gg_shop_variant.sku = Navision_Codes.skuINNER JOIN NAVISION_Cost_Prices ON Navision_Codes."Navision No" =Navision_Cost_Prices.NoWHERE gg_shop_product.pf_id = @pfid)RETURN @ReturnEND

View 3 Replies View Related

User Defined Data Type Used In Stored Procedure Parameters

Jul 23, 2005

I have several stored procedures with parameters that are defined withuser defined data types. The time it takes to run the procedures cantake 10 - 50 seconds depending on the procedure.If I change the parameter data types to the actual data type such asvarchar(10), etc., the stored procedure takes less that a second toreturn records. The user defined types are mostly varchar, but someothers such as int. They are all input type parameters.Any ideas on why the stored procedure would run much faster if notusing user defined types?Using SQL Server 2000.Thanks,DW

View 13 Replies View Related

Setup And Upgrade :: Clarification On Non-integer Constants Are Not Allowed In ORDER BY Clause In 90

May 15, 2015

We're upgrading our SQL Server database from 2005 to 2012.I ran the Upgrade Advisory report and got this issue "Non-integer constants are not allowed in the ORDER BY clause in 90" because of the script below

SELECT
gp.BRAND+' <> '+gp.CATEGORY AS 'full name',
gp.PRODCODE,
gp.CATEGORY
FROM dbo.GFK_PRODUCT gp
ORDER BY
'full name'

I tried running the same query in our test SQL Server 2012 and it ran successfully. Now I'm confuse if i still need to change it.I google the issue a bit and came across this link and mentioned this.

1.) Non-integer constants are ... constants that are not integer number.

Example: 'string1' represents
a string constant
0x01 represents
a varbinary constant
{ts
'2015-02-26 06:00:00'} represents a datetime constant

1.23 represents
a numeric constants

2) So single quotes are used to define a string constants / character string constants but SQL Server allows also to use single quotation marks use also as column identifier
delimiter:
SELECT ... expression AS 'Column1'
FROM ...

In this context is clear that 'Column1' is a column identifier but when used in ORDER BY : ORDER BY 'Column1' it generates confusion because SQL Server doesn't knows if it represents a string literal (character string constant) or it represents a column identifier / column name."Do I still need to change the existing code even though it's working fine in 2012? If yes, it is because of best practice reason or it will total get deprecated/not working in the future version?

View 4 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved