Tweets By List - Fetch

Retrieve tweets authored by members of the specified list, using SQL Server

Prerequisites:

See SQLHTTP easy setup for Twitter
See API Call documentation for parameter values and other information
Create the stored procedure documented below

Stored Procedure:


CREATE PROCEDURE usp_Twitter_List_Statuses_Fetch(
		@Profile nvarchar(100),
		@List_Id bigint,
		@Slug varchar(50),
		@Owner_screen_name varchar(500),
		@Owner_Id bigint,
		@Count bigint,
		@since_id bigint = NULL,
		@max_id bigint = NULL,
		@include_entities bit = 0,
		@include_rts bit = 0,
		@Response nvarchar(MAX) OUTPUT,
		@StatusCode int OUTPUT,
		@StatusDescription nvarchar(MAX) OUTPUT)
AS

DECLARE @Text_include_entities varchar(5)
DECLARE @Text_include_rts varchar(5)

SET @Text_include_entities = 
    CASE
       WHEN @include_entities = 0 THEN 'false'
<span style="color: #ff0000">       ELSE 'true'
<span style="color: #ff0000">    END

SET @Text_include_rts = 
    CASE
       WHEN @include_rts = 0 THEN 'false'
<span style="color: #ff0000">       ELSE 'true'
    END

DECLARE @URL nvarchar(MAX)
DECLARE @QueryString varchar(MAX)
DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://api.twitter.com/1.1/lists/statuses.json'

EXEC SQLHTTP.net.QueryStringBuilder   @QueryString OUTPUT,
				      @Profile,
				      'list_id', @List_Id,
				      'slug', @Slug,
				      'owner_screen_name', @Owner_screen_name,
				      'owner_id', @Owner_Id,
				      'count', @Count,
				      'since_id', @since_id,
				      'max_id', @max_id,
				      'include_entities', @Text_include_entities,
				      'include_rts', @Text_include_rts

SET @URL = @URL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_Twitter_Auth_Header @Profile, @HTTPSessionID, @URL, 'GET'

EXEC SQLHTTP.net.HTTPRequest	@HttpSessionID, 
				@URL = @URL,
				@Method = 'GET',
				@ContentType = 'application/json',
				@Accept = 'application/json',
				@StatusCode = @StatusCode OUTPUT,
				@StatusDescription = @StatusDescription OUTPUT,
				@Response = @Response OUTPUT
GO

CREATE PROCEDURE usp_Twitter_List_Statuses_Fetch(

@Profile nvarchar(100),

@List_Id bigint,

@Slug varchar(50),

@Owner_screen_name varchar(500),

@Owner_Id bigint,

@Count bigint,

@since_id bigint = NULL,

@max_id bigint = NULL,

@include_entities bit = 0,

@include_rts bit = 0,

@Response nvarchar(MAX) OUTPUT,

@StatusCode int OUTPUT,

@StatusDescription nvarchar(MAX) OUTPUT)

DECLARE @Text_include_entities varchar(5)

DECLARE @Text_include_rts varchar(5)

SET @Text_include_entities =

CASE

WHEN @include_entities = 0 THEN 'false'

<span style="color: #ff0000"> ELSE 'true'

<span style="color: #ff0000"> END

SET @Text_include_rts =

CASE

WHEN @include_rts = 0 THEN 'false'

<span style="color: #ff0000"> ELSE 'true'

END

DECLARE @URL nvarchar(MAX)

DECLARE @QueryString varchar(MAX)

DECLARE @HTTPSessionID uniqueidentifier

SET @URL = 'https://api.twitter.com/1.1/lists/statuses.json'

EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT,

@Profile,

'list_id', @List_Id,

'slug', @Slug,

'owner_screen_name', @Owner_screen_name,

'owner_id', @Owner_Id,

'count', @Count,

'since_id', @since_id,

'max_id', @max_id,

'include_entities', @Text_include_entities,

'include_rts', @Text_include_rts

SET @URL = @URL + @QueryString

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC usp_Twitter_Auth_Header @Profile, @HTTPSessionID, @URL, 'GET'

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @URL,

@Method = 'GET',

@ContentType = 'application/json',

@Accept = 'application/json',

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT



Sample Usage:


DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Twitter_List_Statuses_Fetch
		@Profile = 'My Twitter',
		@List_Id = '715919216927322112',
		@Slug = 'national-parks',	
		@Owner_screen_name = 'twitterdev',
		@Owner_Id = '2244994945',
		@Count = '5',
		@Response = @Response OUTPUT, 
		@StatusCode = @StatusCode OUTPUT, 
		@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400
	EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
ELSE
	BEGIN

		DECLARE @X xml
		SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

		--The XPath syntax below was easily generated by executing the following commands:
		--EXEC SQLHTTP.net.XQueryHelper @X
		--and then executing: 
		--EXEC SQLHTTP.net.XQueryHelper @X, 'JsonArray/JsonObject'

		SELECT T.C.value(N'@id', N'nvarchar(MAX)') AS [id]
		      ,T.C.value(N'@text', N'nvarchar(MAX)') AS [text]
		      ,T.C.value(N'user[1]/@name', N'nvarchar(MAX)') AS [user_name]
		FROM @X.nodes(N'/JsonArray/JsonObject') T(C)

	END

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

EXEC usp_Twitter_List_Statuses_Fetch

@Profile = 'My Twitter',

@List_Id = '715919216927322112',

@Slug = 'national-parks',

@Owner_screen_name = 'twitterdev',

@Owner_Id = '2244994945',

@Count = '5',

@Response = @Response OUTPUT,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

BEGIN

DECLARE @X xml

SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0')

--The XPath syntax below was easily generated by executing the following commands:

--EXEC SQLHTTP.net.XQueryHelper @X

--and then executing:

--EXEC SQLHTTP.net.XQueryHelper @X, 'JsonArray/JsonObject'

SELECT T.C.value(N'@id', N'nvarchar(MAX)') AS [id]

,T.C.value(N'@text', N'nvarchar(MAX)') AS [text]

,T.C.value(N'user[1]/@name', N'nvarchar(MAX)') AS [user_name]

FROM @X.nodes(N'/JsonArray/JsonObject') T(C)

END

id text user_name

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

893939795046670337 Want to avoid crowds? 25 miles east... Grand Canyon NPS

893925430012526592 Traffic restrictions at Two Medicine... GlacierNationalPark

893911678135394306 Looks like everyone went to Two Medicine... GlacierNationalPark

893909572729135105 Proud owner of a park pass? You can save time... Grand Canyon NPS



IMPORTANT DISCLAIMER

CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.

USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.

USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.