Store procedure again 1

[Kendel]

Hi All,

I'm trying to convert this scripts into SP:
------
If Cond1 Then
phrase1 = "Where ID between 1 and 100"
Else
phrase1 = "other criteria..."
End If

If Cond2 Then
phrase2 = "criteria.."
Else
phrase2 = "other criteria..."
End If

StrSQL = "Select * from my Table " & phrase1 " & phrase 2 order by Name desc"
-------

I'm learning to use the SP and I don't the synrax to write somethign like this in SP. I want to put everything in the SP, not just the StrSQL. Can someone please help me out?

kendel

 

[zhavic]

For example:

CREATE PROCEDURE my_proc
@nCond1 integer,
@nCond2 integer

DECLARE StrSQL nvarchar(1000)
DECLARE phrase1 nvarchar(100)
DECLARE phrase2 nvarchar(100)

If @nCond1 = 1 Then
SET phrase1 = 'Where ID between 1 and 100'
Else
SET phrase1 = 'other criteria...'
End If

If @nCond2 = 1 Then
SET phrase2 = 'criteria..'
Else
SET phrase2 = 'other criteria...'
End If

SET StrSQL = 'Select * from my Table ' + phrase1 + ' ' + phrase2 + ' order by Name desc'

EXECUTE sp_executesql StrSQL


Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[mihaildamjan]

crate proc MyProc as
-- you have to declare variables first
declare @cond1 as varchar(100),@cond2 as varchar(100)
@phrase1 as varchar(100) , @phrase2 as varchar(100)

If @Cond1 Then
@phrase1 = "Where ID between 1 and 100"
Else
@phrase1 = "other criteria..."
End If

If @Cond2 Then
@phrase2 = "criteria.."
Else
@phrase2 = "other criteria..."
End If
-- end proc

 

[zhavic]

Also remove all 'Then' and 'End if' from my previous post

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[zhavic]

Sorry, am sleeping

This may not have errors:

CREATE PROCEDURE my_proc
@nCond1 integer,
@nCond2 integer

AS

DECLARE @StrSQL nvarchar(1000)
DECLARE @phrase1 nvarchar(100)
DECLARE @phrase2 nvarchar(100)

If @nCond1 = 1
SET @phrase1 = 'Where ID between 1 and 100'
Else
SET @phrase1 = 'other criteria...'

If @nCond2 = 1
SET @phrase2 = 'criteria..'
Else
SET @phrase2 = 'other criteria...'

SET @StrSQL = 'Select * from my Table ' + @phrase1 + ' ' + @phrase2 + ' order by Name desc'

EXECUTE sp_executesql @StrSQL


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Kendel]

Thanks for all the iputs.

Zhavic: I got the error: 'nvarchar' is not a cursor option, incorrect syntax near ='

at line: SET @phrase1 = 'Where ID between 1 and 100'

 

[zhavic]

Can you post there your actual code ?
That from my last post worked for me.


Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Kendel]

Sorry, the eror was "incorrect syntax near '=' "

So, I don't need the 'then' but do I need the 'end if' ?

 

[zhavic]

You ommited '@' before name of variables StrWorkGroup and StrUser

Each variable must start with '@'
( I omitted it too in my first post )

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Kendel]

Eroor at line 12: set StrWorkGroup = ''
and 18: StrUser = ''

 

[Kendel]

Ok, now the erros is "incorrect syntax near keyword 'End' "

 

[zhavic]

Remove 'End if' and please wait, I am writing something to you, because you will get another errors ( with executing @StrSQL )

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[zhavic]

If you want to use this syntax ( like variable @WorkGroup in that string ),

set @StrWorkGroup = ' AND H.WORKGROUP = @WorkGroup'

you need to pass all that variables to the sp_executesql procedure.

So it will look like this:

EXECUTE sp_executesql StrSQL,
N'@WorkGroup nvarchar(100), @UserID int, @BeginDate datetime, @EndDate datetime'
@WorkGroup,
@UserID,
@BeginDate,
@EndDate

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Kendel]

Thank you so much Zhavic. Now I got this: "Syntax check successful!"

Now I need to work on my ASP to call this SP.

 

[zhavic]

Glad I could help.

Good luck with your ASP.

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.