p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

sql_language thread: Parse a string to a string


Message #1 by Richard Hadfield <Richard.Hadfield@o...> on Mon, 4 Mar 2002 12:22:37 -0000
Here is the working solution, it seemed to involve lots of fun with single
quotes to which I still havent fully got around to understanding....any
simple explanations appreciated, the query was also changed to a view
because it was a dynamic query, the underlying objects then all invoked
permissions..., users of the database have only excecute permissions on
procs, thanks for all the feedback from everyone ;)

Richard
----------------------------------------------------------------------------
--------------------------------------------

CREATE PROCEDURE dbo.sp_PinboardIssues_Get
@UserID VARCHAR(20),
@Favourite TINYINT,
@IssueType TINYINT,
@OrderField VARCHAR(40)
AS

DECLARE @sqlQuery NVARCHAR(650)

SET @sqlQuery = 
'SELECT *
FROM vw_PinboardIssues
WHERE chrUser = ' + '''' + @UserID + '''' 
+ ' AND bitFavourite = ' + '''' + CONVERT(CHAR(1), @Favourite) + '''' 
+ ' AND tintType = ' + '''' + CONVERT(CHAR(1), @IssueType) + '''' 
+ ' ORDER BY ' + @OrderField + ''

EXEC sp_ExecuteSQL @sqlQuery

GO

-----Original Message-----
From: jjboja@h... [mailto:jjboja@h...]
Sent: 06 March 2002 02:30
To: sql language
Subject: [sql_language] Re: Parse a string to a string


Richard,

Was looking for a problem to my issue, but decided to respond to yours. I 
had something similar when I was writing my stored procedure:

--...in writing standard "SELECT" statement, it did not understand 
variables well; thus I wrote the entire statement as a string and 
used "EXEC" to convert the string into Transact-SQL, and execute it.
-- Furthermore - in my project - I could not get the "IF" statement to 
work with values produced by "EXEC", so I embedded the entire execution 
into a string, and used "EXEC" to convert it and execute it (maybe for 
future)...

I can't try it, but let me know if this SQL works (just paste the 
following into your query analyzer and note that ' used to escape the 
string):

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

CREATE PROCEDURE dbo.sp_PinboardIssues_Get
  
  @UserID VARCHAR(20),
  @Favourite TINYINT,
  @OrderField VARCHAR(40)
 
  AS

  EXEC (
  'SELECT tblNotify.*, tblIssue.vchrTitle AS ShortDescriptionField,
  tblIssue.dtmLastEdit AS DateField, tblIssue.tintStatus AS StatusField, 
  tblIssue.tintImpact AS ImpactField, tblIssue.txtDescription_Search AS
  LongDescriptionField, tblIssue.smintUserRaised AS UserRaisedByField, 
  tblIssue.smintUserAssigned AS UserAssignedField
  FROM tblNotify, tblIssue, tblUser 
  WHERE tblNotify.chrID = CAST(tblIssue.idIssue AS CHAR) 
        AND tblNotify.tintType = 1
        AND tblUser.idUser = tblNotify.smintUser
        AND tblUser.chrUser = '''+ @userID +''
        )
GO


--------------------------------------------------------------------------
JJ
$subst('Email.Unsub').



NOTICE AND DISCLAIMER:
This email (including attachments) is confidential.  If you have received
this email in error please notify the sender immediately and delete this
email from your system without copying or disseminating it or placing any
reliance upon its contents.  We cannot accept liability for any breaches of
confidence arising through use of email.  Any opinions expressed in this
email (including attachments) are those of the author and do not necessarily
reflect our opinions.  We will not accept responsibility for any commitments
made by our employees outside the scope of our business.  We do not warrant
the accuracy or completeness of such information.


  Return to Index