Question
Matt Nadolny · Jun 21, 2017

Parameters

Hi There

 

New to the cache sql commands looking for how to create a parameter within a sql statement like with MSSQL is @ and Oracle is : 

 

What is cache's?

00
1 0 11 462
Log in or sign up to continue

In Cache ObjectScript p_Branch isn't a valid identifier, because the underscore character is used as the concatenation operator.

Does your attempt work if you change p_Branch to pBranch ?

If not, please show us your SQL in its complete context.

Is your class located within the User package? Ex: User.Location?

If you REALLY want to use _ then at least wrap the alias inside double quotes.

 

Can you provide more details on what exactly you're trying to accomplish? As you said, the last query works. What exactly is missing? Are you trying to get a prompt (outside of Cache I'm guessing) with the parameter name?

Here is the procedure sample:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

 

ClassMethod CalcAvgScore(firstname As %String,lastname As %String) [sqlproc]
{
  New SQLCODE,%ROWID
  &sql(UPDATE students SET avgscore = 
    (SELECT AVG(sc.score) 
     FROM scores sc, students st
     WHERE sc.student_id=st.student_id 
       AND st.lastname=:lastname
       AND st.firstname=:firstname)
     WHERE students.lastname=:lastname
       AND students.firstname=:firstname)

  IF ($GET(%sqlcontext)'= "") {
    SET %sqlcontext.%SQLCODE = SQLCODE
    SET %sqlcontext.%ROWCOUNT = %ROWCOUNT
  }
  QUIT
}

 

Instead of using SQL to define PROCEDURES, even though you can, it's easier to create one using your own class. Just declare it as [ SqlProc] and it'll be available to use inside SQL. You can use that way to define a SQL function as well.

Here is the sql I have attempted

SELECT l.code AS Location_Code,
       l.description AS Location_Name,
       b.code AS Branch_Code,
       b.description AS Branch_Name
FROM LOCATION l,
              branch b ​
WHERE b.code = l.branchcode
  AND b.code = :p_Branch

got and identifier error

Here is the response from management studio window

Input (?) encountered after the of the query^

SELECT l.code AS Location_Code,
       l.description AS Location_Name,
       b.code AS Branch_Code,
       b.description AS Branch_Name
FROM LOCATION l,
              branch b ​
?

While this works and pops up a screen 

SELECT l.code AS Location_Code,
       l.description AS Location_Name,
       b.code AS Branch_Code,
       b.description AS Branch_Name
FROM LOCATION l,
              branch b 
WHERE b.code = l.branchcode
and b.code=?
order by branch_code, Location_Code

Just want to name prompt so when I create the function it will pass the correct value along

The class is SQLUser.location

I am trying to write a store procedure to create and populate a date table for usage by a report tool. I am using this query to prove out the that I pass prompt value like I do in TSQL or PSQL

CREATE function sqlUser.F_TABLE_DATE
(
    ?FIRST_DATE        datetime,
    ?LAST_DATE        datetime
)

Is the beginning of the function