Limiting databases ??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Limiting databases ???

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-19 : 20:25:12

I have the following code that has a parameter called @first (This will be a report parameter). I have used another stored proc temptable to pull all the data into one result set but as you can see by the code I have the EXEC TestMultipleDB @first = 'ray' which is a problem because @first needs to be a variable, come from a report parameter and not be fixed at 'ray'.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[TestMultipleDB] Script Date: 08/19/2008 18:14:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[TestMultipleDB]
@first varchar (30)
AS

BEGIN

declare @sql varchar(max)
select @sql=''
select @sql=@sql+ 'SELECT USERID, FirstName, LastName FROM '+name+'.dbo.UserDetails Where FirstName IN (Select Param From fn_MVParam ('''+@first+''','','')) ' from sys.databases where name='VC' or name like 'VCA%'
exec(@sql)

END

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[TempTable] Script Date: 08/20/2008 09:52:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[TempTable]
AS

CREATE TABLE #Temp
([USERID] int, [FirstName] varchar(50), [LastName] varchar(50))

INSERT INTO #Temp ([USERID], [FirstName], [LastName])
EXEC TestMultipleDB @first = 'ray' <<<How do I change this to accept @first as the other procedure does?>>>

---INSERT INTO #Temp ([USERID], [Description], Qty)
---EXEC Sproc2

SELECT DISTINCT [USERID], [FirstName], [LastName]
FROM #Temp

DROP TABLE #Temp

RETURN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 00:05:26

for executing sp with dynamic values you need to either call it inside loop with value extracted for each sp call and passing it to EXEC. other alternative is to capture all the values you want sp to be called it and pass them together as csv and in sp you will parse and use them one by one.
For making temp table dynamic you need to call it using dynamic sql. and pas required column names too inside it.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-20 : 21:18:48

Sorry I still don't know what to do. Do you have example of how or specifically how to change the code above? I am a beginner.
The parameter is also a multi-value.

The TempTable always returns nothing else you specify the value exactly hence the need to pass the parameter values?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 04:00:23

<<<How do I change this to accept @first as the other procedure does?>>>

Can you explain with more details?
Do you want to pass more than a value?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 04:14:16

quote:
Originally posted by harlingtonthewizard

Sorry I still don't know what to do. Do you have example of how or specifically how to change the code above? I am a beginner.
The parameter is also a multi-value.

The TempTable always returns nothing else you specify the value exactly hence the need to pass the parameter values?

before i give you an example. can you some info on what values you will be passing to sp and what should you be getting as result from your table for them by giving sample data and output you desire from sp.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-21 : 04:52:59

I am trying to use a simple example to learn which may be my undoing:) There will typically be several parameters of differing datatypes. The parameters are used within Reporting Services 2005 (The drop down multivalued lists or calendars etc). Here is an example of two procedures I am working on at the moment. If run alone the first procedure returns rows for the specified columns, given the where clause filtering from various tables for each database that matches the condition. This is a problem because I get one result set for each database and I need one result set in total for reporting services so this is where the second procedure comes in. By calling the first procedure from the second it stores the result sets from all db's into one temp table hence giving me the one result set I need. The problem is that I only know how to fix the report parameter variables in the exec command to one fixed value when I need them to be multivalued. The result sets below as I currently have 3 db's but this could be 1 to x db's.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Users] Script Date: 08/21/2008 16:45:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Users]
@CombinedName varchar (100),
@CombPriv varchar (100)
AS

declare @sql varchar(max)
select @sql=''
select @sql=@sql+ 'SELECT UserDetails.LastName + '' '' + UserDetails.FirstName AS CombinedName,
GroupDetails.GroupName + '' - '' + PermissionDetails.PermissionName AS CombPriv, UserDetails.FirstName, UserDetails.MiddleName,
UserDetails.LastName, UserDetails.Initials, UserDetails.LoginName, GroupDetails.GroupName, GroupDetails.GroupDescription,
FeatureDetails.Description, PermissionDetails.PermissionName, PermissionDetails.PermissionDescription, UserDetails.UserID
FROM '+name+'.dbo.UserDetails INNER JOIN
'+name+'.dbo.UserGroup ON '+name+'.dbo.UserDetails.UserID = '+name+'.dbo.UserGroup.UserID INNER JOIN
'+name+'.dbo.GroupDetails ON '+name+'.dbo.UserGroup.GroupID = '+name+'.dbo.GroupDetails.GroupID INNER JOIN
'+name+'.dbo.GroupFeatures ON '+name+'.dbo.GroupDetails.GroupID = '+name+'.dbo.GroupFeatures.GroupID INNER JOIN
'+name+'.dbo.FeatureDetails ON '+name+'.dbo.GroupFeatures.FeatureID = '+name+'.dbo.FeatureDetails.FeatureID INNER JOIN
'+name+'.dbo.GroupPermissions ON '+name+'.dbo.GroupDetails.GroupID = '+name+'.dbo.GroupPermissions.GroupID INNER JOIN
'+name+'.dbo.PermissionDetails ON '+name+'.dbo.GroupPermissions.PermissionID = '+name+'.dbo.PermissionDetails.PermissionID
WHERE (UserDetails.LastName + '' '' + UserDetails.FirstName) IN (Select Param From fn_MVParam ('''+@CombinedName+''','','')) AND (GroupDetails.GroupName + '' - '' + PermissionDetails.PermissionName) IN (Select Param From fn_MVParam ('''+@CombPriv+''','','')) AND (UserDetails.IsDeleted = ''False'')
ORDER BY CombinedName ' from sys.databases where name='VC' or name like 'VCA%'
Print @sql
exec(@sql)

Return

<<<<Note only one row is returned because this the data input matches my where condition and this person only exists in the one DB.>>>>

Running [dbo].[Users] ( @CombinedName = harlington steve, @CombPriv = operator - permission2 ).

CombinedName CombPriv FirstName MiddleName LastName Initials LoginName GroupName GroupDescription Description PermissionName PermissionDescription UserID
----------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
Harlington Steve Operator - Permission2 Steve James Harlington SJH SJH Operator OperatorDes FeatureDes2 Permission2 PerDes2 2108
No rows affected.
(1 row(s) returned)
CombinedName CombPriv FirstName MiddleName LastName Initials LoginName GroupName GroupDescription Description PermissionName PermissionDescription UserID
----------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
No rows affected.
(0 row(s) returned)
CombinedName CombPriv FirstName MiddleName LastName Initials LoginName GroupName GroupDescription Description PermissionName PermissionDescription UserID
----------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[Users].

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Users_TempTable] Script Date: 08/21/2008 16:54:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Users_TempTable]

AS

CREATE TABLE #Temp
([FirstName] varchar(50), [LastName] varchar(50), [CombinedName] varchar(100), [CombPriv] varchar(100), [MiddleName] varchar(50), [Initials] varchar(50), [LoginName] varchar(50), [GroupName] varchar(50), [GroupDescription] varchar(50), [PermissionName] varchar(50), [PermissionDescription] varchar(50), [Description] varchar(50), [USERID] int)

INSERT INTO #Temp ([CombinedName], [CombPriv], [FirstName], [MiddleName], [LastName], [Initials], [LoginName], [GroupName], [GroupDescription], [Description], [PermissionName], [PermissionDescription], [USERID])
EXEC Users <<<Error occurs because no parameters specified>>>>

SELECT DISTINCT [CombinedName], [CombPriv], [FirstName], [MiddleName], [LastName], [Initials], [LoginName], [GroupName], [GroupDescription], [Description], [PermissionName], [PermissionDescription], [USERID]
FROM #Temp

DROP TABLE #Temp

RETURN

<<<<I fixed input as EXEC Users @CombinedName = 'Harlington Steve', @CombPriv = 'Operator - Permission2'>>>> If other matches were made from other db's then they would also appear in this one result set>>>>

Running [dbo].[Users_TempTable].

CombinedName CombPriv FirstName MiddleName LastName Initials LoginName GroupName GroupDescription Description PermissionName PermissionDescription USERID
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------
Harlington Steve Operator - Permission2 Steve James Harlington SJH SJH Operator OperatorDes FeatureDes2 Permission2 PerDes2 2108
(1 row(s) affected)
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[Users_TempTable].

This is one of the other procedures that returns the options for the drop down list in reporting services.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Users_RP] Script Date: 08/21/2008 18:13:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Users_RP]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

-- Insert statements for procedure here
SELECT LastName + ' ' + FirstName AS CombinedName, FirstName, LastName, UserID
FROM dbo.UserDetails
ORDER BY CombinedName
END

Running [dbo].[Users_RP].

CombinedName FirstName LastName UserID
----------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------
Harlington Steve Steve Harlington 2108
Not Archive Not Archive Not Archive Not Archive 2106
No rows affected.
(2 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[Users_RP].

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 05:09:20

No need of second procedure. Directly use like this

create table #t(dbname varchar(100))
insert into #t
EXEC sp_msforeachdb 'select ''?'''
select dbname from #t

Madhivanan

Failing to plan is Planning to fail

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-21 : 09:48:14

This will loop through all db's right? I was limiting the db's to:
from sys.databases where name='VC' or name like 'VCA%

How do I limit db's, is it something like:

Create table #t(dbname varchar(100))
Insert into #t
EXEC sp_msforeachdb IF EXISTS 'USE ? SELECT ''?'' '
Select dbname
Drop table #t

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109295

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-22 : 03:24:55

Create table #t(dbname varchar(100))

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' select name from master..sysdatabases where name =''VC'' or name like ''VCA%'''
Insert into #t
EXEC (@sql)
Select dbname from #t
Drop table #t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources