SQL server connection: procedure

Showing posts with label procedure. Show all posts

Tuesday, March 20, 2012

Another Way To Write a Stored Procedure

In the stored procedure below, there are 9 different if conditions being
checked. I was wondering if there is a more efficient way (or not) to write
this procedure, without having to write the same select criteria each time.
The only thing different is the where clause. Any suggestions are greatly
appreciated.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROC ListParticipantsByRole(
@.testRoleEnum int,
@.errorID int = 0 OUTPUT)
AS
SET NOCOUNT ON
-- List Originators
IF (@.testRoleEnum = 1)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleOriginator = 1
ORDER BY P.DisplayName
-- List Validators
IF (@.testRoleEnum = 2)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleValidator = 1
ORDER BY P.DisplayName
-- List Screeners
IF (@.testRoleEnum = 3)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleScreener = 1
ORDER BY P.DisplayName
-- List SMEs
IF (@.testRoleEnum = 4)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleSME = 1
ORDER BY P.DisplayName
-- List Validation OPRs
IF (@.testRoleEnum = 5)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleValidationOPR = 1
ORDER BY P.DisplayName
-- List Validation Authorities
IF (@.testRoleEnum = 6)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleValidationAuthority = 1
ORDER BY P.DisplayName
-- List Officials
IF (@.testRoleEnum = 7)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleOfficial = 1
ORDER BY P.DisplayName
-- List Test Directors
IF (@.testRoleEnum = 8)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleTestDirector = 1
ORDER BY P.DisplayName
-- List Survey Participants
IF (@.testRoleEnum = 9)
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE T.TestRoleSurveyParticipant = 1
ORDER BY P.DisplayName
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE 1 = CASE @.testRoleEnum
when 1 then T.TestRoleOriginator
when 2 then T.TestRoleValidator
...
end
ORDER BY P.DisplayName
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:C4C1B5B8-7E49-467A-83C7-4DEEED88B57B@.microsoft.com...
> In the stored procedure below, there are 9 different if conditions being
> checked. I was wondering if there is a more efficient way (or not) to
> write
> this procedure, without having to write the same select criteria each
> time.
> The only thing different is the where clause. Any suggestions are greatly
> appreciated.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
> AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||Mike,
A redesign of TestParticipants is the best solution. Instead of
having a bunch of separate columns TestRoleThis, TestRoleThat,
TestRoleOther, ... you should have, depending on whether
a participant can have more than one role, either a column TestRole,
with possible values 'Validator', 'Screener', etc., or a separate table
TestParticipantRoles that links the participants with their roles.
Then your stored procedure becomes a single simple query like
this (case where there is a linking table):
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN TestParticipantRoles R ON T.PersonnelID = R.PersonnelId
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE R.RoleID = @.testRoleEnum
ORDER BY P.DisplayName
Short of that, you can still now do
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE CASE @.testRoleEnum
WHEN 1 THEN T.TestRoleOriginator
WHEN 2 THEN T.TestRoleValidator
..
END = 1
ORDER BY P.DisplayName
This latter solution may turn out to have poorer performance, however.
Steve Kass
Drew University
Mike Collins wrote:

>In the stored procedure below, there are 9 different if conditions being
>checked. I was wondering if there is a more efficient way (or not) to write
>this procedure, without having to write the same select criteria each time
.
>The only thing different is the where clause. Any suggestions are greatly
>appreciated.
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
>AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>|||Well, a more efficient design would store the numeric coefficient in a
single column, instead of having 9 different columns. It's hard to tell
from your requirements exactly what solution you need (e.g. can someone be a
screener and a roleSME?). But this is certainly not the best approach.
When you add or remove a role type, you have to change table structure and
all the code that references it. Blecch.
If a personnel member can only be in one role, then this design makes more
sense, IMHO:
CREATE TABLE dbo.TestRoles
(
TestRoleEnum INT PRIMARY KEY,
Description VARCHAR(32) UNIQUE
)
SET NOCOUNT ON;
INSERT dbo.TestRoles(Description) SELECT 'Originator';
...
CREATE TABLE dbo.TestParticipants
(
Personnelid INT /* FOREIGN KEY... */,
TestRoleEnum INT
)
Now your stored procedure can say:
ALTER PROCEDURE dbo.ListParticipantsByRole
@.testRoleEnum int,
@.errorID int = 0 OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT
P.DisplayName,
T.Personnelid
FROM
dbo.TestParticipants t
INNER JOIN
Common.dbo.vwPersonnelInfo p
ON
t.Personnelid = P.personellid
WHERE
t.TestRoleEnum = @.testRoleEnum
ORDER BY
P.DisplayName;
END
GO
And now you can add and remove test roles as you please, without having to
change any of the database structure. And as a bonus, your code becomes a
lot easier to read.
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:C4C1B5B8-7E49-467A-83C7-4DEEED88B57B@.microsoft.com...
> In the stored procedure below, there are 9 different if conditions being
> checked. I was wondering if there is a more efficient way (or not) to
> write
> this procedure, without having to write the same select criteria each
> time.
> The only thing different is the where clause. Any suggestions are greatly
> appreciated.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
> AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||try this.. and see if it works faster...
writing for 2 ofthem.. u can add the rest..
Hope this helps.
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE @.testRoleEnum = 1 and T.TestRoleOriginator = 1
ORDER BY P.DisplayName
union all
SELECT P.DisplayName, T.PersonnelID
FROM TestParticipants T
JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
WHERE @.testRoleEnum = 2 and T.TestRoleValidator = 1
ORDER BY P.DisplayName|||Thank you all for the quick and great responses. I see now better ways of
designing my tables and how to better write my stored procedure. In this
scenario, a person can have more than one role.
"Mike Collins" wrote:

> In the stored procedure below, there are 9 different if conditions being
> checked. I was wondering if there is a more efficient way (or not) to writ
e
> this procedure, without having to write the same select criteria each tim
e.
> The only thing different is the where clause. Any suggestions are greatly
> appreciated.
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> ALTER PROC ListParticipantsByRole(
> @.testRoleEnum int,
> @.errorID int = 0 OUTPUT)
> AS
> SET NOCOUNT ON
> -- List Originators
> IF (@.testRoleEnum = 1)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOriginator = 1
> ORDER BY P.DisplayName
> -- List Validators
> IF (@.testRoleEnum = 2)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidator = 1
> ORDER BY P.DisplayName
> -- List Screeners
> IF (@.testRoleEnum = 3)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleScreener = 1
> ORDER BY P.DisplayName
> -- List SMEs
> IF (@.testRoleEnum = 4)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSME = 1
> ORDER BY P.DisplayName
> -- List Validation OPRs
> IF (@.testRoleEnum = 5)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationOPR = 1
> ORDER BY P.DisplayName
> -- List Validation Authorities
> IF (@.testRoleEnum = 6)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleValidationAuthority = 1
> ORDER BY P.DisplayName
> -- List Officials
> IF (@.testRoleEnum = 7)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleOfficial = 1
> ORDER BY P.DisplayName
> -- List Test Directors
> IF (@.testRoleEnum = 8)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleTestDirector = 1
> ORDER BY P.DisplayName
> -- List Survey Participants
> IF (@.testRoleEnum = 9)
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE T.TestRoleSurveyParticipant = 1
> ORDER BY P.DisplayName
> SET NOCOUNT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||If you are planning to use a case statement, I would suggest going for a
union all
like I mentioned since it helps in better use of indexes, if any. If you
have not indexed the columns in the where clause, then go for the case. But
,
if you are redesining the table, thats the best way.|||That's a fairly straightforward approach. And here I was going to suggest
dynamic SQL.
Of course, the various suggestions to change the table structure are much
better in the long run.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:u9$RbXPeGHA.564@.TK2MSFTNGP02.phx.gbl...
> SELECT P.DisplayName, T.PersonnelID
> FROM TestParticipants T
> JOIN Common..vwPersonnelInfo P ON T.PersonnelID = P.PersonnelId
> WHERE 1 = CASE @.testRoleEnum
> when 1 then T.TestRoleOriginator
> when 2 then T.TestRoleValidator
> ...
> end
> ORDER BY P.DisplayName
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:C4C1B5B8-7E49-467A-83C7-4DEEED88B57B@.microsoft.com...
greatly
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:O9EDeiPeGHA.3556@.TK2MSFTNGP02.phx.gbl...
> That's a fairly straightforward approach. And here I was going to suggest
> dynamic SQL.
> Of course, the various suggestions to change the table structure are much
> better in the long run.
I'm getting lazy. :-)|||Thanks for the additional input. Unfortunately it is not my decision to
redesign the database and I might have to use the case statements. I am
definitely going to give my two cents about redesigning the database.
"Omnibuzz" wrote:

> If you are planning to use a case statement, I would suggest going for a
> union all
> like I mentioned since it helps in better use of indexes, if any. If you
> have not indexed the columns in the where clause, then go for the case. Bu
t ,
> if you are redesining the table, thats the best way.sql

Monday, March 19, 2012

Another Stored Procedure Question...

Hi,

Is it possible whithin a Stored Procedure send the table name as a parameter?
And some Columns to?

Ex.:

CREATE Procedure Xpto

@.TableName as ?
@.ColumnName as ?
@.SomeValue as nvarchar(10)

SELECT * FROM @.TableName WHERE @.ColumnName = @.SomeValue

Thanks

JPP

create procedure SelectFrom @.table sysname, @.column sysname, @.value sysname
as
exec ('select * from '+ @.table + ' where ' +@.column + ' = ''' + @.value + '''')

|||

You should protect the code above against SQL injection attacks like below:

declare @.tablename nvarchar(130), @.columnname nvarchar(130), @.sql nvarchar(4000)

set @.tablename = quotename(@.table)

set @.columnname = quotename(@.column)

set @.sql = 'select * from ' + @.tablename + ' where ' + @.columnname + ' = @.value'

exec sp_executesql @.sql, N'@.value nvarchar(4000)', @.value = @.value

However, there shouldn't be a need to write such generic stored procedures. It is not a good thing to do. You have to grant permissions to users since dynamic SQL is evaluated at run-time. So please write a stored procedure per table/feature/module that can handle the data access for you. Alternatively, you can also create views and expose the data.

|||Hi,

When I post this question I was thinking on using the solution to dynamicly update some tables with the Numers of the documents.
Ex.:

Table Numbers
NroInvoice
NroReceipt
....

Then, to update one of the numbers I dont have to write a stored procedure for each on.
Do you think this is a bad aprotch?

Thank you for your time.

JPP|||Another reason to avoid such generic stored procedures is that they cannot be optimized and do not generate a pre-compiled execution plan that would be kept for repeated execution.|||Hello...

I would not use a table to hold values like this...

Those information is already in the DB and there is no need to denormalize it. If there are propper indexes on those fields you want to evaluate you can wrtite a small view that extracts the data you need...

By the way... Why is there no SQL Code button on the form ;)

create view DocCount
as
select (select count(*) from Documents where type = 'Invoice') NrOfInvoices,
(select count(*) from Documents where type = 'Mail') NrOfMails

or another way...

create view DocCount2
as
select type, count(*) from Documents group by type

This way there is no need to update this table

Another Stored procedure problem with VB 2005

Solving one problem leads to another. This post is related to my last one (Can't see stored proc results) so this will look familiar to some of you. This sproc is called by a VB front end. It retrieves a name based on an ID#:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_CrimRecTest]
-- Add the parameters for the stored procedure here
@.caseID nvarchar(12)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @.caseID)
END

I am also looking for problems on the VB side if it's not here.

It executes fine on the SQL Server side. I took an option to preview the data through the VB dataset that this sproc fills. After entering the required parm, instead of the name I want, I see this:
Type Value
Int32 0
I previewed a view with the same select statement minus the WHERE clause. It worked fine. Any reason why the sproc shows up differently?
Thank you for all of the help.

If it executes fine in the SQL Server side (and you see results?) then there's nothing wrong with the proc. You have to look at the VB side. Does the VB code take care of the returned resultset as it should?

/Kenneth

|||I have been looking into that. Being new to both SQL Server and VB, it takes me a while to find where the real problem is at times. I have determined that the parm is not getting to the sproc correctly. I also found that the VB program is receiving a return value instead of the data set for both of the sprocs I have.|||

I found a way around this on the VB side. Some built in functions are now giving me the results I need.

Thanks for the reply, Kenneth.

Another SQL Stored Procedure Question

Thanks for the help with the last question... I finally got it to work... I have a new problem tho with the same setup -

I need to create a table in the Access97 database and then populate it with SQL Server2k data. I'm using a SELECT ... INTO statement (which works fine in the SQL Enterprise Manager QRY thingy (I'm soooo technical) but it's not working when the stored procedure is called. The Access97 database is set up as a linked server, I'm pulling the database name in thru a parameter along with the condition. Maybe one of you can see something I don't with this code?

CREATE PROCEDURE dbo.usp_PutData @.DbName nvarchar(20), @.State nvarchar(4), @.AirType nvarchar(10)
AS

declare @.QueryIs as varchar(8000)

select @.QueryIs = Case upper(@.AirType)
when 'NFDC'then "SELECT (FldName1, FldName2, FldName3, FldName4, FldName5, FldName6, FldName7) INTO " + @.DbName + "...Facility FROM tbl_NFDC_Facility WHERE (Assoc_State = '" + @.State + "')"
end

Select @.QueryIs, @.DbName
exec (@.QueryIs)
GO

Any and all help is greatly appreciated!Did you cut and paste this code directly from MSSQAT (Microsoft SQL Server Query Analyzer Thingy)? Your use of double quotes is a syntax error. You have to use single quotes, and tripled single quotes to represent an embedded single quote.

Also, you use too many dots qualifying your table name.

@.DbName + "...Facility

should be

@.DbName + "..Facility

otherwise SQL Server will look be looking for a server named after your database. A fully qualified table reference looks like this:
[ServerName].[DatabaseName].[Owner].[Table]

Also, be aware that there are some connection settings that may have different defaults in Query Analyzer than other DB interfaces. For instance, SET CONCAT_NULL_YIELDS_NULL { ON | OFF } defaults to ON for SQL Query Analyzer, but defaults to OFF in Crystal reports.

blindman

Another Reporting System Stored Procedure Issue

Thx to all who helped me for Stored Procedure previously

Here is what I have:
3 Drop Down Boxes:
1) List of property
2) Ticket Status
3) Tech Name

All 3 Drop boxes have default value of "All"

So, if all 3 drop boxes are "All" ie.

list of property = All
Ticket status = All
Tech Name = All

Query pulls up all records from database and displays it.

Lets say if I select Tech Name is XYZ then query should pull out all property, all ticket status by Tech XYZ.

Now my previous developer has if else case and he has total 9 query for doing all this. He has used SQL along with C# code.

I am trying to modify this if-else and convert it into Stored Procedure. Is there a way I can handle all with 1 stored procedure ?

Previous Reporting works like charm......Assuming Property and TicketStatus are ints and TechName is character and assuming that -1 for @.Property and @.TicketStatus means "ALL" and that an empty string for @.TechName means "ALL" then:


CREATE PROCEDURE SprocName
@.Property int = -1,
@.TicketStatus int = -1
@.TechName varchar(20) = ''Select x,y,x
From SomeTable
WHERE
 (Property= CASE WHEN @.Property != -1 THEN @.Property ELSE Property END) AND
 (TicketStatus = CASE WHEN @.TicketStatus != -1 THEN @.TicketStatus ELSE TicketStatus) END) AND
 (TechName = CASE WHEN @.TechName != '' THEN @.TechName ELSE TechName END)

The where clause resolves to Property = Property if @.Property is -1 which selects all values.
If @.Property is some value like 10 then the where clause resolves to Property = 10.
And so on for the other conditions.|||The poster asked this same question on another thread (view post 421457). My suggestion was similar to yours, but used COALESCE instead:


CREATE PROCEDURE myTest
@.ListOfProperty varchar(100) = NULL,
@.TicketStatus varchar(100) = NULL,
@.TechName varchar(100) = NULLAS
SELECT
 column1,
 column2,
 <etc>
FROM
 myTable
WHERE
 ListOfProperty = COALESCE(@.ListOfProperty,ListOfProperty) AND
 TicketStatus = COALESCE(@.TicketStatus,TicketStatus) AND
 TechName = COALESCE(@.TechName,TechName)

Do you have any idea which approach would be better?

Terri|||Cool. I'll have to try that next time. It makes the code a lot cleaner.|||COALESCE doesn't seem to work. I will try to work with the quesry with COALESCE one more time...

Thursday, March 8, 2012

another dynamic assembly loading problem (XmlSerializer)

I am having an issue with loading dynamic generated assemblies in my CLR SQL stored procedure. I have tried turning on the "Generate serialization assembly: ON" and have read numerous articles regarding work around but have been very unsuccessful in getting this to work.

The problem lies in when the code calls

XmlSerializer xmlSerializer = new XmlSerializer(typeof(MyClass));

where MyClass is a generated schema class using XSD. Since it wouldn't auto generate the serializer assembly, what I did instead was created a new class library project with only that line of code and provided the class definition within that same class without any of the attributes from the XSD. I am still getting an error on that same line. So what I did next was do the manual sgen and made sure it generated with the same signed assembly snk file. Manually loaded into sql server and still got the same error. Any ideas why this is?

Code Snippet

public partial class TestClass
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void testThis()
{
MyOwnClass myClass = new MyOwnClass();
//Serialize message to xml
XmlSerializer xmlSerializer = new XmlSerializer(typeof(MyOwnClass));
}
}

public class MyOwnClass
{
private string hi;
public MyOwnClass()
{
hi = "1";
}
public string Hi
{
get
{
return hi;
}
}
}

Your code works fine for me. Are you sure you are creating the XmlSerializers assembly correctly under SQL Server? When you turn "Generate Serializaton Assembly" to on under VS, it will create the assembly but not deploy it to the database for you, so you need to create it yourself like so:

create assembly [TestClass.XmlSerializers] from 'c:\assemblies\TestClass.XmlSerializers.dll'

Also, what is the exact error message and call stack you are getting?

Steven

|||

In order to get Visual Studio to actually build a serialization assembly, you have to both

1. Set the "Generate Serialization Assembly" to ON, and

2. Add, by hand, the following SGEN task to your project file:

</SGen>

</Target>

There is, however, a major problem with this approach: The SGEN task will only execute AFTER all post-build events have fired. Thus, if you have a post-build event that copies the serialization assembly to, say, a deployment folder, it will copy the old one because the copy occurs before the SGEN task is executed. This problem is best resolved by running SGEN from a post-build event like so:

sgen /a Embarrassed (ProjectDir)$(OutDir)$(ProjectName).dll /compiler:/keyfile(ProjectDir)My_Key_File.snk /f

But this is where your problems really begin. The only way I have been able to get SQL CLR to actually load the serialization assembly is to apply the XmlSerializationAssembly attributes to my serializable classes AND load the serialization assemblies into the GAC and SQL CLR. I have described this in detail in a previous post and it is the ONLY way I am able to invoke XML serialization from within a SQL CLR procedure.

Developing SQL CLR components is substantially more difficult than it needs to be due to issues just like this - and documentation is almost impossible to find for all but the most trivial use cases. I was very excited about the integration of SQL Server and .Net. After having worked a bit with this technology though, I am much less enthusiastic....

|||Steve - the assembly is not getting generated with the "Generate Serializaton Assembly" set to On. What I have been doing is like what NTDeveloper said (manually using SGEN). I then manually deploy that serializer assembly into SQL.

The error message i'm getting is the typical one that everyone sees

System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[])

NTDeveloper - I can do it the way you mentioend in your post, but I don't need the XmlSerializationAssembly attributes, I just need to deploy it to GAC. I don't like this approach however...
|||

TCHEN,

You are absolutely correct; I too was able to get the assemblies to load by ONLY putting them into the GAC and NOT attaching the XmlSerializationAssembly attribute. Now how in the heck are we going to get this to work without putting these things in the GAC ? This is also something I would very much like to avoid. According to Steve, SQL CLR will not load assemblies from the GAC unless the are on an internal, predefined list controlled by Microsoft. Obviously though, the GAC somehow plays a role here though; otherwise, the installation of these assemblies to the GAC would be irrelevant. Moreover, as I noted in another post, if the MVID (which gets regenerated each build) of the assembly in the GAC does not match the MVID assembly loaded into SQL CLR, you will get an exception that informs you of this.

I am at a loss.

|||

I believe I have figured out what the problem is. The XmlSerialization assembly binding code will only load a strong-named assembly if it has an Assembly Version specified. I'm really not sure why this is, but I'll try to find out. If there is no Version specified, then the sgened assembly will not be found and it will try to load one dynamically, which of course fails. As NTDeveloper found, one workaround for this is to add the versionless .XmlSerializers assembly to the GAC. Note that the actual XmlSerializers assembly will still be loaded from the database, the gac check occurs before the assembly attempts to load (I believe this check is done because the CLR can skip verifying the assembly strong name if it is loaded from the GAC).

So, in short, in order to use XmlSerialization on a signed assembly in SQL CLR, your assembly must be annotated with an assembly version:

[assembly:AssemblyVersion("1.2.3.4")]

[assembly:AllowPartiallyTrustedCallers]

public class ...

(AllowPartiallyTrustedCallers is also required for Strong Named assembly, as per http://blogs.msdn.com/sqlclr/archive/2006/06/22/643554.aspx).

Hopefully this solves your problem. There is certainly no argument from me that this is a pretty complicated process.

Steven

|||

Steven,

Thanks for your response.

Unfortunatey, I don't believe the version of the assembly is the issue. When SGEN generates a serialization assembly S for an assembly A, it automatically versions S with the version of A. Thus, since all of my assemblies are versioned the serialization assemblies are versioned as well.

Any other ideas?

Thanks,

Chris

|||Hmm, no I was sure it was going to be the AssemblyVersion issue as that is the only case I could see that fits your repro. Without a repro that I can debug, I'm really not sure what else could be causing this behavior. Can you try setting the Fusion ForceLog on and checking the Fusion Log to see what the output is when it tries to load the XmlSerializers assembly? This log should show exactly why it failed to load the pregenerated assembly.

Steven|||

You actually may end up being right about the assembly version. Here's the deal:

I have FINALLY managed to successfully invoke XML serialization from INSIDE SQL Server WITHOUT installing to the GAC !!! Other than the general difficulty of running managed code inside SQL Server (which, by the way, is considerably more difficult than it should be), I believe that the root of the problem was using Visual Studio to build the serialization assemblies via the SGEN build task. Although I changed my makefile, er, project file, to use the sgen.exe utility to generate the assemblies, I was still inadvertantly using the SGEN build task and overwriting the assemblies generated via sgen.exe. Once I completely wiped out the SGEN build task and relied only on the sgen.exe to build my serialization assemblies, everything began to work like MAGIC. Voila! No more need to deploy to the GAC, thus no need to deploy FROM the server, etc, etc. It may be that the SGEN build task manages to create unversioned assemblies; however, since I have completely removed these build tasks and the assemblies generated by it, I can't say for sure. At some point, I am going to create a test project and see whether this is the case and I will post a follow-up here indicating the results.

Thanks for your help on this misery-inducing problem!

|||NTDeveloper,
I'm a little confused. When you say SGEN build task, do you mean you set "Generate Serialization Assemblies" to ON?

Thanks.
|||Steve,

I followed NTDeveloper's advice and started to manually sgen the serializer assembly instead of installing it to the GAC, and i'm still getting an error. Here is the latest stack trace.

here is my sgen command

"$(DevEnvDir)..\..\SDK\v2.0\bin\sgen.exe" /f /a Embarrassed (TargetPath) /c:/keyfile(ProjectDir)StrongMail.snk

A .NET Framework error occurred during execution of user-defined routine or aggregate "SendNotificationEmail":
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CShar
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
|||

Yes; You have to set then "Generate Serialization Assemblies" to ON

AND

Manually define the SGEN build task within your project file like so:

</SGen>

</Target>

HOWEVER, as I indicated in the post above, a serialization assembly generated in this fashion will not load correctly inside SQL CLR. As Steve suggested, it may be a versioning issue; I just don't know. I haven't gone back and generated an assembly in this fashion and checked the version information.

After using the SGEN.exe tool did you uninstall the previously generated assemblies from the GAC? If not, this may be the reason why you can't get the new serialiazation assembly to load properly. After I switched to using the SGEN.exe tool, instead of the Visual Studio build task, this issue hasn't recurred.

|||Yes I checked the GAC...its empty. I had Generate Serialization to OFF since i'm using the SGEN.exe tool rather than doing it through visual studio. Even with this manner its failing.

If I do it with an empty task with XmlSerializer = new XmlSerializer(MyClass) however..that works. But with the use of making a webservice call, it fails with the error above.

btw the Fusion log only logs exceptions to disk from visual studio it seems. none of my sql clr stuff is getting logged.
|||

I was also unable to get the Fusion tool to work for SQL CLR.

Have you used the SGEN.exe tool to create a serialization assembly for your SOAP proxy class(es)?

|||

Can you post the code you're using to call the webservice? Are you sure the error occurs while attempting to serialize MyOwnClass vs. a different class? It seems the XmlSerializer assembly can be loaded correctly for MyOwnClass, so perhaps the problem is elsewhere (such as trying to serialize a class shipped in a system assembly).

Steven

Wednesday, March 7, 2012

Another DBCC DBREINDEX Question

I am interested in finding out what would happen if I were to call a stored procedure that reindexes a specific table (SQL Server 2000) from .NET code. Specifically, is DBCC DBREINDEX a blocking call (my current assumption is that it in fact is)? I am calling this procedure from a secondary thread, while my primary thread executes other tasks. I think this is a feasible solution, but I just wanted to make sure if my assumption is correct.

Also, I am using a trusted connection, but I am not sure how to treat the SqlConnection object once I call the SPROC; I won't be able to close the connection until the SPROC returns - what would the immediate consequence of keeping a connection open for a long time be (if DBCC DBREINDEX is a blocking call, then my connection will stay open until the SPROC returns). (if DBCC DBREINDEX is a blocking call, then my connection will stay open until the SPROC returns)?

Thanks in advance!

Edit: I forgot to mention, but this whole task is being executed as a scheduled task from the command line - so I also have the option of using osql, but again am not sure about the consequences of calling DBCC DBREINDEX from osql.

i am not sure i understand the requirement... nevertheless this hints may help u..... DBCC DBREINDEX is a offline command... ie. the table will not be available for any pupose while re-index is going on. If the another thread you mentioned is accessing same table .. then it will be blocked. if it is accessing another table then there is no issue

Madhu

|||

Madhu K Nair wrote:

i am not sure i understand the requirement... nevertheless this hints may help u..... DBCC DBREINDEX is a offline command... ie. the table will not be available for any pupose while re-index is going on. If the another thread you mentioned is accessing same table .. then it will be blocked. if it is accessing another table then there is no issue

Madhu

Maybe I confused you...

The SPROC that I am calling will call DBCC REINDEX: .NET Code > SqlConnection > DBCC REINDEX

What I am asking is what will happen if I call a the stored procedure that contains the T-SQL to reindex the table; will my .NET thread block until this SPROC returns (which could theoretically take hours)? Here is a simple layout of my T-SQL SPROC:

CREATE PROC ReindexArchive

BEGIN

DBCC DBREINDEX('TableName', '', 70)

WITH NO_INFOMSGS

END

So what I am trying to ask is if this sproc gets called from .NET code, will that call be blocked until the table is reindexed and the SPROC returns control to the calling .NET code. Thanks.

|||

i think you need to create multi threads here. otherwise , you will have to wait till the REINDEX Procedure completes to go to next step

Madhu

Saturday, February 25, 2012

Annoying, cant insert into DB for some reason, even using a stored procedure.

Hello, I am having problems inserting information into my DB.

First is the code for the insert


Sub AddCollector(Sender As Object, E As EventArgs)
 Message.InnerHtml = "" If (Page.IsValid)
 Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database='MyCollection'"
 Dim myConnection As New SqlConnection(ConnectionString)
 Dim myCommand As SqlCommand
 Dim InsertCmd As String = "insert into Collectors (CollectorID, Name, EmailAddress, Password, Information) values (@.CollectorID, @.Name, @.Email, @.Password, @.Information)"
 myCommand = New SqlCommand(InsertCmd, myConnection)
 myCommand.Connection.Open()
 myCommand.Parameters.Add(New SqlParameter("@.CollectorID", SqlDbType.NVarChar, 50))
 myCommand.Parameters("@.CollectorID").Value = CollectorID.Text
 myCommand.Parameters.Add(New SqlParameter("@.Name", SqlDbType.NVarChar, 50))
 myCommand.Parameters("@.Name").Value = Name.Text
 myCommand.Parameters.Add(New SqlParameter("@.Email", SqlDbType.NVarChar, 50))
 myCommand.Parameters("@.Email").Value = EmailAddress.Text
 myCommand.Parameters.Add(New SqlParameter("@.Password", SqlDbType.NVarChar, 50))
 myCommand.Parameters("@.Password").Value = Password.Text
 myCommand.Parameters.Add(New SqlParameter("@.Information", SqlDbType.NVarChar, 3000))
 myCommand.Parameters("@.Information").Value = Information.Text
 Try
 myCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>"
 Catch Exp As SQLException
 If Exp.Number = 2627
 Message.InnerHtml = "ERROR: A record already exists with the same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record"
 End If
 Message.Style("color") = "red"
 End Try
 myCommand.Connection.Close()
 End If
End Sub

No matter what I get a "Could not add record" message

Even substituting the insert command string with my stored procedure I would get the same thing

Stored Procedure:


CREATE Procedure CollectorAdd
 (
 @.Name nvarchar(50),
 @.Email nvarchar(50),
 @.Password nvarchar(50),
 @.Information nvarchar(3000),
 @.CustomerID int OUTPUT
 )
 AS INSERT Collectors
 (
 Name,
 EMailAddress,
 Password,
 Information
 )
 VALUES
 (
 @.Name,
 @.Email,
 @.Password,
 @.Information
 )
GO

Can anyone see any problems with this code? It looks good to me but I get the same message always.

ThanksWhy not print out the actual exception text (exp.ToString(), for instance)? Then the exception will tell you why.|||Wow, nice little trick. It helped me find the problem. I had an expected parameter in the SP that I was not supplying.

Thank You

Announcing the Analysis Services Stored Procedure Project

A few months ago, a few community-spririted Analysis Services guys (including me) got together to create some example Analysis Services stored procedures. I'm happy to announce that beta 1 of our project is now available to download here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures
The idea was to create a set of useful extensions to MDX to help solve common problems and at the same time provide some example source code to help people writing their own stored procedures. Please take a look and tell us what you think!

Very Nice!

How about a function that takes care of divide-by-zero and returns NULL if the denominator is 0? Ie. ReturnDivide(division). That would clean up a lot of iif mdx.

|||

We discussed this exact problem, but Mosha explained that using a sproc in this way would do more harm than good. Basically, the problem is that there's no way of marking a sproc as being deterministic (ie will always return the same result for the same cell) and so that means that if you use a sproc in a calculated member then the value returned by that calculation will never be cached. As a result, it's probably better to use IIF instead so that subsequent requests for the result returned by a calculation for any given cell in the cube will be returned from the cache.

To answer your other question about IIF, if <mdxstatement> is a calculated measure whose result can be cached, then no, it will only be executed once and the second time it's evaluated the value will be returned from the cache. So it's good idea to create a calculated measure to hold the value of <mdxstatement> even if you don't intend to display the result to the user and set its Visible property to False.

HTH,

Chris

|||

Just so I understand:
You are saying that the following MDX script will make the server calculate [measures].[summation] once in the scope iif statement:

Create Member [Measures].[Summation] AS

Aggregate({[DimMember1], [DimMember2]});

Scope ([DimMemberX]);

this = iif([Measures].[Summation] = 0, NULL, [SomeSet] / [Mesures].[Summation];

End Scope;

Whereas this statement will make it calculate it twice:

Scope ([DimMemberX]);

this = iif(aggregate({[Dimmember1], [Dimmember2]}) = 0, NULL, [Someset] / aggregate([DimMember1], [DimMember2]));

End Scope;

|||

Yes, that's what I understand.

Chris

Friday, February 24, 2012

Annotated Mapping Schema

I have a stored procedure that uses the FOR XML EXPLICIT mode to return an xml view of my data. I then insert this xml into another table.
Instead of writing complicated T-SQL with the FOR XML EXPLICIT mode I'd like to use an xsd schema, however I can't find examples that let you apply the transformation in a stored procedure / test it in Query Analyzer. All the examples I've found involve m
apping the schema in the URL or using a SqlXmlCommand object.
Any help would be very much appreciated!
Cheers,
Paul
Mapping Schemas are a client-side technology - actually, they just generate
FOR XML EXPLICIT statements on the server (you can see this by running a
trace when retrieving data with a schema). As such, there's no way to
reference them from within a T-SQL sproc. Sorry!
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Paul Bibby" <Paul Bibby@.discussions.microsoft.com> wrote in message
news:4B7E0882-5EED-4A7F-987D-125262683276@.microsoft.com...
I have a stored procedure that uses the FOR XML EXPLICIT mode to return an
xml view of my data. I then insert this xml into another table.
Instead of writing complicated T-SQL with the FOR XML EXPLICIT mode I'd like
to use an xsd schema, however I can't find examples that let you apply the
transformation in a stored procedure / test it in Query Analyzer. All the
examples I've found involve mapping the schema in the URL or using a
SqlXmlCommand object.
Any help would be very much appreciated!
Cheers,
Paul

Sunday, February 19, 2012

ancestor using XQuery in SQL Server 2005

Hi all,
I'm need to use the ancestor axis in an XQuery statemen. However, i receive
the next error message:
Msg 9335, Level 16, State 1, Procedure pa_Sales_Test, Line 76
XQuery [query()]: The XQuery syntax 'ancestor' is not supported.
Does anyone know how to workaround the problem?
This is an example of the XQuery statement:
SELECT @.xml.query('
for $GM in descendant::dsc:GeneratedMaterial
return
(
<GeneratedMaterial
Reference="{ data($GM/@.material) }"
Color="{ data($GM/@.color) }"
Quantity="{ data($GM/@.count) }"
Length="{ data($GM/@.length) }"
Height="{ data($GM/@.height) }"
AncestorNode="{ data($GM/ancestor::*/Hole/@.id) }"
/>
)
')
The ancestor Hole may be located anywhere in the xml, as well as the
GeneratedMaterial node.
Thanks in Advance,
Alberto.You can find all the hole elements in the document that have the current
GeneratedMaterial as a descendant. Here is an example:
CREATE
--ALTER
FUNCTION BData()
RETURNS XML
AS
BEGIN
RETURN N'<build>
<stuff>
<hole id="1">
<more>
<GM material="A" color="red"/>
<GM material="B" color ="blue"/>
</more>
</hole>
<hole id="2">
<more>
<GM material="C" color="red"/>
<GM material="D" color ="blue"/>
</more>
</hole>
<hole id="3"></hole>
<hole id="10">
<hole id="4">
<more>
<GM material="F" color="red"/>
<GM material="G" color ="blue"/>
</more>
</hole>
</hole>
<GM material="H" color ="blue"/>
</stuff>
</build>'
END
SELECT dbo.BData().query(
'
for $GM in (descendant::GM)
return element GM {
attribute material {$GM/@.material},
attribute color {$GM/@.color},
attribute AncestorId {
(:
get the holes that has this GM as a descendant,
that is that are an ancestor of GM
:)
//hole[descendant::GM[. is $GM]]/@.id}
}
')
---
<GM material="A" color="red" AncestorId="1" />
<GM material="B" color="blue" AncestorId="1" />
<GM material="C" color="red" AncestorId="2" />
<GM material="D" color="blue" AncestorId="2" />
<GM material="F" color="red" AncestorId="10 4" />
<GM material="G" color="blue" AncestorId="10 4" />
<GM material="H" color="blue" AncestorId="" />
Note that some GM elements in this document have more than one hole as an
ancestor and others have no holes and ancestors.
Dan

> Hi all,
> I'm need to use the ancestor axis in an XQuery statemen. However, i
> receive
> the next error message:
> Msg 9335, Level 16, State 1, Procedure pa_Sales_Test, Line 76
> XQuery [query()]: The XQuery syntax 'ancestor' is not supported.
> Does anyone know how to workaround the problem?
> This is an example of the XQuery statement:
> SELECT @.xml.query('
> for $GM in descendant::dsc:GeneratedMaterial
> return
> (
> <GeneratedMaterial
> Reference="{ data($GM/@.material) }"
> Color="{ data($GM/@.color) }"
> Quantity="{ data($GM/@.count) }"
> Length="{ data($GM/@.length) }"
> Height="{ data($GM/@.height) }"
> AncestorNode="{ data($GM/ancestor::*/Hole/@.id) }"
> />
> )
> ')
> The ancestor Hole may be located anywhere in the xml, as well as the
> GeneratedMaterial node.
> Thanks in Advance,
> Alberto.
>|||Hi Dan,
Great example. Just one question:
What does //hole mean?
Does it return all hole(s) of the document or just the ones in the second
level of the xml?
"Dan" <dsullivanATdanal.com> escribi en el mensaje
news:964a9ae651b1d8c85db66c8af312@.news.microsoft.com...
> You can find all the hole elements in the document that have the current
> GeneratedMaterial as a descendant. Here is an example:
> CREATE --ALTER
> FUNCTION BData()
> RETURNS XML
> AS
> BEGIN
> RETURN N'<build>
> <stuff>
> <hole id="1">
> <more>
> <GM material="A" color="red"/>
> <GM material="B" color ="blue"/>
> </more>
> </hole>
> <hole id="2">
> <more>
> <GM material="C" color="red"/>
> <GM material="D" color ="blue"/>
> </more>
> </hole>
> <hole id="3"></hole>
> <hole id="10">
> <hole id="4">
> <more>
> <GM material="F" color="red"/>
> <GM material="G" color ="blue"/>
> </more>
> </hole>
> </hole>
> <GM material="H" color ="blue"/>
> </stuff>
> </build>'
> END
>
> SELECT dbo.BData().query(
> '
> for $GM in (descendant::GM)
> return element GM {
> attribute material {$GM/@.material}, attribute color {$GM/@.color},
> attribute AncestorId {
> (:
> get the holes that has this GM as a descendant,
> that is that are an ancestor of GM
> :)
> //hole[descendant::GM[. is $GM]]/@.id}
> }
> ')
> ---
> <GM material="A" color="red" AncestorId="1" />
> <GM material="B" color="blue" AncestorId="1" />
> <GM material="C" color="red" AncestorId="2" />
> <GM material="D" color="blue" AncestorId="2" />
> <GM material="F" color="red" AncestorId="10 4" />
> <GM material="G" color="blue" AncestorId="10 4" />
> <GM material="H" color="blue" AncestorId="" />
>
> Note that some GM elements in this document have more than one hole as an
> ancestor and others have no holes and ancestors.
> Dan
>
>
>|||//hole is a synonym for /descendant-or-self::hole which means all the hole
elements in the document.
Dan
> Hi Dan,
> Great example. Just one question:
> What does //hole mean?
> Does it return all hole(s) of the document or just the ones in the
> second
> level of the xml?
> "Dan" <dsullivanATdanal.com> escribi en el mensaje
> news:964a9ae651b1d8c85db66c8af312@.news.microsoft.com...
>

Tuesday, March 20, 2012

Another Way To Write a Stored Procedure

Monday, March 19, 2012

Another Stored Procedure Question...

Another Stored procedure problem with VB 2005

Another SQL Stored Procedure Question

Another Reporting System Stored Procedure Issue

Thursday, March 8, 2012

another dynamic assembly loading problem (XmlSerializer)

Wednesday, March 7, 2012

Another DBCC DBREINDEX Question

Saturday, February 25, 2012

Annoying, cant insert into DB for some reason, even using a stored procedure.

Announcing the Analysis Services Stored Procedure Project

Friday, February 24, 2012

Annotated Mapping Schema

Sunday, February 19, 2012

ancestor using XQuery in SQL Server 2005

SQL server connection

Blog Archive

About Me