Parameters within a sql statement

Question

Question

Matt Nadolny · Jun 21, 2017

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?

Discussion (11)0

Log in or sign up to continue

John Murray · Jun 21, 2017

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.

0 0

Rubens Silva · Jun 22, 2017

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.

0 0

score 1 · Answer 1 · 2017-06-21T13:56:31-04:00

Are you using Dynamic SQL (%ResultSet, %SQL.Statement)?

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

Are you using embedded SQL or named queries?

Then it's the same as Oracle. Ex: :parameterName

score 0 · Answer 2 · 2017-06-22T09:58:10-04:00

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.

score 0 · Answer 3 · 2017-06-22T11:21:51-04:00

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?

score 0 · Answer 4 · 2017-06-21T17:03:27-04:00

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

score 0 · Answer 5 · 2017-06-22T09:49:50-04:00

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 
?

score 0 · Answer 6 · 2017-06-22T10:22:50-04:00

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

score 0 · Answer 7 · 2017-06-22T10:23:56-04:00

Matt Nadolny · Jun 22, 2017

The class is SQLUser.location

0 0

score 0 · Answer 8 · 2017-06-22T12:15:40-04:00

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

score 0 · Answer 9 · 2017-06-22T12:20:16-04:00

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

Is the beginning of the function