Sample code: A few sample stored procs
satya - 4/22/2014 3:24:30 PM
sp_getFilesForTagsAndFolders.sql
/*
***********************************************************************
* sp_getFilesForTagsAndFolders.sql
* args: owneruserid
* ignored args: a) list of folder ids, b) list of tag names
*
* This is a test stored proc at this time
* You can make this prodution ready by splitting the args
* (tbd) make it production ready
* currently this sql is used directly in the source code
*
* (Not required at the moment in prod)
***********************************************************************
*/
use test;
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*Drop the object if available */
if exists (select * from sysobjects
where id = object_id(N'[dbo].[sp_getFilesForTagsAndFolders]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_getFilesForTagsAndFolders]
GO
/*
* tag a folder given a folder id and a document id
*/
CREATE PROCEDURE dbo.sp_getFilesForTagsAndFolders
@inFolderIds varchar,
@inTagName varchar,
@inOwnerUserId varchar(50)
as begin
SELECT *, f.owner_user_id as folder_owner_user_id
FROM filed_items AS fi,
reports AS r,
folders AS f
WHERE 1=1
And fi.item_id=r.report_id
And fi.folder_id=f.folder_id
and f.[public]='Y'
and (f.owner_user_id=@inOwnerUserId
or f.folder_id in (282,285)
or f.folder_id in (
--folders whose tag name is 'android'
select vtf.folder_id
from v_taggedFolders vtf
where 1=1
and vtf.f_tagname in ('android')
) -- close tagged folders
) -- close and
ORDER BY r.last_updated_on DESC
;
end
GO
/**
* Complete the process
*/
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
satya - 4/22/2014 3:25:11 PM
sp_getFoldersForTag.sql
/*
***********************************************************************
* (not required)
* sp_getFoldersForTag.sql
* sp_getFoldersForTag(tagname)
*
* given a tag name give me all the folders
* does not pay attention to the owner
*
* probably you can use the view
* (not required in prod)
* only for testing at the moment
***********************************************************************
*/
use test;
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*Drop the object if available */
if exists (select * from sysobjects
where id = object_id(N'[dbo].[sp_getFoldersForTag]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_getFoldersForTag]
GO
/*
* Givena tag return all folders
* make sure the tag is public
* Should return folders from all users with that tag name
*/
CREATE PROCEDURE dbo.sp_getFoldersForTag
@inTagName varchar(200)
as begin
select tags.f_target_id
from
t_relationships tags, reports d
where 1=1
and tags.f_source_id = d.report_id
and d.report_short_name like @inTagName
--and tags.f_public = 'Y'
;
end
GO
/**
* Complete the process
*/
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
satya - 4/22/2014 3:25:55 PM
Creating a view
/*
***********************************************************************
* v_taggedFolders.sql
* this is a view
* f_relationship_id,
* f.folder_name,
* f.folder_id,
* d.report_short_name as f_tagname,
* d.report_id as f_tagid
*
* Goal:
* folder and its tag names
* this is used by home page url
* to return folders that match a tag name or names
*
* (required in prod)
***********************************************************************
*/
USE [test]
GO
IF EXISTS (SELECT * FROM sys.views
WHERE object_id = OBJECT_ID(N'[dbo].[v_taggedFolders]'))
DROP VIEW [dbo].[v_taggedFolders]
GO
USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_taggedFolders]
AS
select tags.f_relationship_id,
f.folder_name,
f.folder_id,
d.report_short_name as f_tagname,
d.report_id as f_tagid,
d.owner_user_id as f_tag_owner_userid
from folders f, t_relationships tags, reports d
where 1=1
and tags.f_target_domain = 1
and tags.f_target_id = f.folder_id
and tags.f_source_domain = 2
and tags.f_source_id = d.report_id
and d.report_id in (
select fi.item_id
from folders ssf, filed_items fi
where 1=1
and ssf.folder_name = 'tags'
and ssf.folder_id = fi.folder_id );
GO
satya - 4/22/2014 3:34:38 PM
A series of tests that were run in the query analyzer or a sample sql file
/*
*Test
*/
use test;
go
/*
exec sp_getFilesForTagsAndFolders "282,285", "android", "satya"
go
select tags.f_target_id
from
t_relationships tags, reports d
where 1=1
and tags.f_source_id = d.report_id
and d.report_short_name in ('android')
select f.owner_user_id, f.folder_name, f.folder_id, r.report_short_name, r.report_id from
folders f, filed_items fi, reports r
where 1=1
and f.folder_id = fi.folder_id
and fi.filed_item_id = r.report_id
and f.folder_name = 'tags'
select r.report_id from
folders f, filed_items fi, reports r
where 1=1
and f.folder_id = fi.folder_id
and fi.filed_item_id = r.report_id
and f.folder_name = 'tags'
select f.owner_user_id, f.folder_name, f.folder_id, r.report_short_name, r.report_id from
folders f, filed_items fi, reports r
where 1=1
and f.folder_id = fi.folder_id
and fi.filed_item_id = r.report_id
and f.folder_name = 'tags';
select * from reports
where report_short_name = 'android'
select * from filed_items
where filed_item_id = 4053
go
delete from t_relationships;
go
exec tagFolder 18,183,'satya'
exec tagFolder 18,94,'satya'
exec tagFolder 18,550,'satya'
exec tagFolder 18,551,'satya'
go
select * from t_relationships;
go
--exec sp_getFoldersForTag "%bject%"
--go
--exec sp_getTagsForFolder 18, 'satya'
--go
--exec sp_getPossibleTagsForFolder 18, 'satya'
--go
select tags.f_target_id
from
t_relationships tags, reports d
where 1=1
and d.report_id in (
select r.report_id from
folders f, filed_items fi, reports r
where 1=1
and f.folder_id = fi.folder_id
and fi.filed_item_id = r.report_id
and f.folder_name = 'tags'
)
and tags.f_source_id = d.report_id
-- and d.report_short_name in ('android')
and d.report_short_name in ('android')
go
exec sp_getFilesForTagsAndFolders "282,285", "android", "satya"
go
*/
SELECT *, f.owner_user_id as folder_owner_user_id
FROM filed_items AS fi, reports AS r, folders AS f
WHERE 1=1 And
fi.item_id=r.report_id
And fi.folder_id=f.folder_id
and f.[public]='Y'
and (f.owner_user_id='android'
or f.folder_id in (null) or
f.folder_id in (
select vtf.folder_id
from v_taggedFolders vtf
where 1=1 and
vtf.f_tagname in ('android') ) )
ORDER BY r.last_updated_on DESC
satya - 1/3/2020, 2:15:33 PM
You can do this: Line continuation in calling a stored procedure
sp_insert_someproc "Test Plant Name"
,10 --lat
,10 --long
,1 --solar plant
,22.3 --megawatts
,5 --turbines
,2 --inverters
,"Solar power plant with 5 turbines and 2 inverters"
,4 --primary contact
,"someuser";